Blogger Sandbox

The MAX() function gets the highest value in the specified column, and the MIN() function gets the lowest value in the specified column SELECT MAX(UnitPrice) AS HighestPrice, MIN(UnitPrice) AS LowestPrice FROM Products The query above gets the highest and lowest prices for the Products table in the Northwind database  

The DATEPART function extracts the date part of a date, for example using the 'yyyy' expression allows you to extract the year from a given date. The query below queries all the employees who were hired in the year 1994 in the Northwind Employees table. SELECT FirstName + ' ' + LastName AS Employee, HireDate FROM Employees WHERE DATEPART(yyyy,HireDate) = 1994 SELECT FirstName + ' ' + LastName AS Employee, HireDate FROM Employees WHERE DATEPART(MM,HireDate) = 10 The query above returns the records of employees who were hired on October

The AVG() function gets the average of a column, the following query gets the average of the UnitPrice column in the Northwind Products table. SELECT AVG(UnitPrice) AS AveragePrice FROM Products

The COUNT() function returns the number of rows in the specified table. There are two ways you can use COUNT(), which are the following: COUNT(*) count all the rows in the table including COUNT(column) return all the rows that contains value for the column, excluding the columns with null value SELECT COUNT(*) AS NumberOfRows FROM Customers The query above returns the number of rows in the Customers table SELECT COUNT(Region) AS NumberOfRows FROM Customers The query above counts the number of rows for the column "Region" that are not NULL

The GridView data grid control is probably the most popular and flexible data grid control in the ASP.NET arsenal.  In this tutorial we will create a new GridView data grid on a web form. Here are the steps: 1. Click on the "Design" tab in main window of Visual Studio 2. Click on the "Toolbox" tab, then expand the "Data" node, then drag the "GridView" data control to the Design surface.  The Design Surface is the big window in the middle of Visual Studio. 3. Click on "Toolbox" tab again, this time drag the "SqlDataSource" control to the Design Surface 4. Click on the ">" button next to SqlDataSource1 control, then select "Configure Data Source" 5. Click on the "New Connection" button  6. Select your "Server name", SQL Server should pick up your database server automatically if it's on a local machine, but if it doesn't type in (local) in the "Server name" field.   7....

When you need to do an insert into multiple database table you need to the get the ID of the insert so that you could use that ID for the next insert. Here is how you would do that with the Scope_Identity()which gets the last inserted ID back to you if you execute your query with the ExecuteScalar() method. SqlCommand cmd = new SqlCommand("INSERT INTO Users (" + "LoginName," + "FirstName," + "LastName," + "Password," + "Email," + "DOB," + "Sex" + ") VALUES (" + "@Email," + "@FirstName," + "@LastName," + "@Password," + "@Email," + "@DOB,"...

string dobStr = ddlMonth.SelectedValue + "/" + txtDay.Text + "/" + txtYear.Text; SqlParameter dob = new SqlParameter("DOB", Convert.ToDateTime(dobStr)); dob.SqlDbType = SqlDbType.Date; cmd.Parameters.Add(dob);

In this example we will use the SqlDataAdapter to fill a DataTable then bind the data to a GridView  1. Drag a GridView into your ASP.NET page 2. Use the following code to the fill the dtProducts DataTable with the adapter SqlDataAdapter, then bind it to the GridView1 control. protected void Page_Load(object sender, EventArgs e) { DataTable dtProducts = new DataTable(); string connString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"]. ConnectionString; using (SqlConnection conn = new SqlConnection(connString)) { SqlCommand cmd = new SqlCommand("SELECT * FROM Products", conn); conn.Open(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(dtProducts); GridView1.DataSource = dtProducts; GridView1.DataBind(); } }

The NOT IN operator in SQL means that you are retrieving records in the database that does not match the values in a comma separated list. In other words it retrieves the inverse of the IN statement by itself. Here is an example of how you can use the IN operator in the products table in the Northwind database. SELECT * FROM Products WHERE SupplierID NOT IN (1,2) The above example all the products will be retrieved except for products with SupplierID of 1 or 2, here are the results

The IN operator in SQL means that you are retrieving records in the database that matches the values in a comma separated list. Here is an example of how you can use the IN operator in the products table in the Northwind database. SELECT * FROM Products WHERE SupplierID IN (1,2) In the above example all the products with the SupplierID of 1 or 2 are retrieved.

SQL Server as well as other DBMS has an order of evaluation that can throw you off. Especially when you have more than one comparison in the WHERE clause. In this example I will show you the difference between using a parentheses and not using one, and how by using parentheses can give the results that you want.  Suppose you want to get the products with CategoryID 1 and 2 that are priced less than 15 dollars in the Products table in the Northwind database. Here is the query without the parentheses: SELECT CategoryID,ProductName,UnitPrice FROM Products WHERE CategoryID = 1 OR CategoryID =2 AND UnitPrice < 15 When you run the query above you would expect that all the records retrieved will have a unit price of less than $15 dollar but that is not the case. Below is the result from the query. As you can see several records have unit price that are greater than $15 dollars Now let's run the query with parantheses SELECT CategoryID,ProductName,UnitPrice FROM Products WHERE (Cate...

Retrieve records with NULL value in the ShippedDate column in the Orders table in Northwind SELECT OrderID, ShippedDate FROM Orders WHERE ShippedDate IS NULL Retrieve records that is does not have NULL value in the ShippedDate column in the Orders table in Northwind SELECT OrderID, ShippedDate FROM Orders WHERE ShippedDate IS NOT NULL

Let's say you want to know the orders that takes place in the Northwind database table Orders tables that occurs during the Christmas Eve 1997-12-24 and the New Years Day the following year in 1998-01-01. Here is the SQL to query the OrderID between those date range: SELECT OrderID, OrderDate FROM Orders WHERE OrderDate BETWEEN '1997-12-24' AND '1998-01-01'

The Categories table is a perfect example of how sometimes you have to populate the DropDownList control to data from the database. In this example we will populate the DropDownList control to the Categories table in the Northwind database.  1. Drag the DropDownList control into a .aspx page. Make sure you check "Enable AutoPostBack" 2.  In your C# code file you need the namespaces using System.Web.Configuration; using System.Data.SqlClient; using System.Data; 2. Then get the Northwind connection string value from the Web.config file string connectString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"]. ConnectionString; 3. Type in the following code in the Page_Load method protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { using (SqlConnection conn = new SqlConnection(connectString)) { SqlCommand cmd = new SqlCommand(...

In this example we will pass an input parameter to determine the result of the query.  We will pass in supplier id to get all the products that are provided by the supplier. Here is the code Use Northwind GO CREATE PROCEDURE dbo.selProductsBySupplierID @SupplierID int AS SELECT DISTINCT p.ProductID, p.ProductName, p.UnitPrice FROM Products p INNER JOIN Suppliers s ON p.SupplierID = @SupplierID GO The @SupplierID is the input parameter that the stored procedure expects, you use it in the join portion of the statement to match the SupplierID in the Products table. The DISTINCT keyword means that the query will only return one row in the result if there are duplicate rows. Type the following to execute the stored procedure with the supplier id of 1 EXEC dbo.selProductsBySupplierID 1 The result should look like this

So you have following connection string to the Northwind database in your Web.config and you want to connect to the database in the code behind instead using query builder. <connectionStrings> <add name="NorthwindConnectionString" connectionString="Data Source=(local); Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data. SqlClient"/></connectionStrings> Here is how you would do it 1. Get the connection from the Web.config file programmatically, you can look at this blog to find out how perform this step by step.  But here is the code to get the connection string from the Web.config file string connectString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"]. ConnectionString; 2.  On the top of the file specify that you want to use the Sql.Data.SqlClient namespace by typing using System.Data.SqlClient; 3. Now type the following using (SqlConnection conn = new SqlConnection(...

Out of the box the ASP.NET provider databases works with the local database file call aspnetdb.mdf, however in a production environment you cannot use this file because you need to work with the production database. To create the provider databases on a full version of SQL Server perform the following steps. 1. Navigate to the .NET Framework version that you want to use. Version 4.5 and Version 2 has the provider database wizard. I am using version 4.5 so the path is C:\Windows\Microsoft.NET\Framework\v4.0.30319 2. Double click on the file aspnet_regsql.exe, the ASP.NET SQL Server Setup Wizard will apear, click "Next" to continue 3.  Select "Configure SQL Server for application services" radio button, then click next 4. Type in the database server name in the "Server:" field.  Make sure you type in the actual host name because if you type in (local) or localhost the database creation process will fail.   Leave the option as "Windows authentication...

The SqlDataSource control provides you with a quick and easy way to access database data.  Most of the time you don't even have to write a single line of code.  Now this can a be a good thing and a bad thing, depends on how you look at things.  Most of the configuration and querying is performed via a configuration wizard. Here are the steps to creating a SqlDataSource: 1. In a ASP.NET project/web site drag a SqlDataSource control in under "Data" in the "Toolbox" tab into a .aspx page.   After you dragged the control it should look like this on your .aspx page 2. Click on "SqlDataSource1" control, then click on the ">" sign, a slide out menu appears.  Select "Configure Data Source". The "Configure Data Source" wizard will pop up. 3. Click on the "New Connection" button, the "Add Connection" wizard will pop up. Select or type in your "Server name".  If you have a local instance of SQL Server...