There are manby things which are considered as basics in dot net nuke.When you are using host sql window from dnn then most definitely you will need to have ideas of basic sql statements to fetch info from db.Few of them are:-
Listing all tables from DataBase:-
SELECT * FROM information_schema.Tables
Get Stored procedure description and Structure:-
select * from information_schema.routines where routine_name = ‘Stored Procedure Name’
Querry to fetch all tables in a database:
select * from sysobjects where type=’u’
Querry to fetch all procedures in a database:
select * from sysobjects where type=’p’
Display Table Creation And Owner info
sp_help table_name
Display Table structure and columns:-
sp_columns @table_name = ‘table_name‘
this will show all fields from table table_nameincluding data type and length information.
Return a list of objects that can be queried in the current environment
EXEC sp_tables
Return information about the syscolumns table in the Company database
EXEC sp_tables syscolumns, dbo, Company, "'SYSTEM TABLE'"
Altering/modifying cloumn Width :-
alter table table_name ALTER COLUMN column_name varchar(30)
This lesson describes the SqlCommand object and how you use it to interact with a data base. Here are the objectives of this lesson:
Know what a command object is.
Learn how to use the ExecuteReader method to query data.
Learn how to use the ExecuteNonQuery method to insert and delete data.
Learn how to use the ExecuteScalar method to return a single value.
Introduction
A SqlCommand object allows you to specify what type of interaction you want to perform with a data base. For example, you can do select, insert, modify, and delete commands on rows of data in a data base table. The SqlCommand object can be used to support disconnected data management scenarios, but in this lesson we will only use the SqlCommand object alone. A later lesson on the SqlDataAdapter will explain how to implement an application that uses disconnected data. This lesson will also show you how to retrieve a single value from a data base, such as the number of records in a table.
Creating a SqlCommand Object
Similar to other C# objects, you instantiate a SqlCommand object via the new instance declaration, as follows:
SqlCommand cmd = new SqlCommand(”select CategoryName from Categories”, conn);
The line above is typical for instantiating a SqlCommand object. It takes a string parameter that holds the command you want to execute and a reference to a SqlConnection object. SqlCommand has a few overloads, which you will see in the examples of this tutorial.
Querying Data
When using a SQL select command, you retrieve a data set for viewing. To accomplish this with a SqlCommand object, you would use the ExecuteReader method, which returns a SqlDataReader object. We’ll discuss the SqlDataReader in a future lesson. The example below shows how to use the SqlCommand object to obtain a SqlDataReader object:
// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand(”select CategoryName from Categories”, conn);
// 2. Call Execute reader to get query results SqlDataReader rdr = cmd.ExecuteReader();
In the example above, we instantiate a SqlCommand object, passing the command string and connection object to the constructor. Then we obtain a SqlDataReader object by calling the ExecuteReader method of the SqlCommand object, cmd.
This code is part of the ReadData method of Listing 1 in the Putting it All Together section later in this lesson.
Inserting Data
To insert data into a data base, use the ExecuteNonQuery method of the SqlCommand object. The following code shows how to insert data into a data base table:
// prepare command string string insertString = @”
insert into Categories
(CategoryName, Description)
values (’Miscellaneous’, ‘Whatever doesn”t fit elsewhere’)”;
// 1. Instantiate a new command with a query and connection SqlCommand cmd = new SqlCommand(insertString, conn);
// 2. Call ExecuteNonQuery to send command cmd.ExecuteNonQuery();
The SqlCommand instantiation is just a little different from what you’ve seen before, but it is basically the same. Instead of a literal string as the first parameter of the SqlCommand constructor, we are using a variable, insertString. The insertString variable is declared just above the SqlCommand declaration.
Notice the two apostrophes (”) in the insertString text for the word “doesn”t”. This is how you escape the apostrophe to get the string to populate column properly.
Another observation to make about the insert command is that we explicitly specified the columns CategoryName and Description. The Categories table has a primary key field named CategoryID. We left this out of the list because SQL Server will add this field itself. trying to add a value to a primary key field, such as CategoryID, will generate an exception.
To execute this command, we simply call the ExecuteNonQuery method on the SqlCommand instance, cmd.
This code is part of the Insertdata method of Listing 1 in the Putting it All Together section later in this lesson.
Updating Data
The ExecuteNonQuery method is also used for updating data. The following code shows how to update data:
// prepare command string string updateString = @”
update Categories
set CategoryName = ‘Other’
where CategoryName = ‘Miscellaneous’”;
// 1. Instantiate a new command with command text only SqlCommand cmd = new SqlCommand(updateString);
// 2. Set the Connection property cmd.Connection = conn;
// 3. Call ExecuteNonQuery to send command cmd.ExecuteNonQuery();
Again, we put the SQL command into a string variable, but this time we used a different SqlCommand constructor that takes only the command. In step 2, we assign the SqlConnection object, conn, to the Connection property of the SqlCommand object, cmd.
This could have been done with the same constructor used for the insert command, with two parameters. It demonstrates that you can change the connection object assigned to a command at any time.
The ExecuteNonQuery method performs the update command.
This code is part of the UpdateData method of Listing 1 in the Putting it All Together section later in this lesson.
Deleting Data
You can also delete data using the ExecuteNonQuery method. The following example shows how to delete a record from a data base with the ExecuteNonQuery method:
// prepare command string string deleteString = @”
delete from Categories
where CategoryName = ‘Other’”;
// 1. Instantiate a new command SqlCommand cmd = new SqlCommand();
// 2. Set the CommandText property cmd.CommandText = deleteString;
// 3. Set the Connection property cmd.Connection = conn;
// 4. Call ExecuteNonQuery to send command cmd.ExecuteNonQuery();
This example uses the SqlCommand constructor with no parameters. Instead, it explicity sets the CommandText and Connection properties of the SqlCommand object, cmd.
We could have also used either of the two previous SqlCommand constructor overloads, used for the insert or update command, with the same result. This demonstrates that you can change both the command text and the connection object at any time.
The ExecuteNonQuery method call sends the command to the data base.
This code is part of the DeleteData method of Listing 1 in the Putting it All Together section later in this lesson.
Getting Single values
Sometimes all you need from a data base is a single value, which could be a count, sum, average, or other aggregated value from a data set. Performing an ExecuteReader and calculating the result in your code is not the most efficient way to do this. The best choice is to let the data base perform the work and return just the single value you need. The following example shows how to do this with the ExecuteScalar method:
// 1. Instantiate a new command SqlCommand cmd = new SqlCommand(”select count(*) from Categories”, conn);
// 2. Call ExecuteNonQuery to send command int count = (int)cmd.ExecuteScalar();
The query in the SqlCommand constructor obtains the count of all records from the Categories table. This query will only return a single value. The ExecuteScalar method in step 2 returns this value. Since the return type of ExecuteScalar is type object, we use a cast operator to convert the value to int.
This code is part of the GetNumberOfRecords method of Listing 1 in the Putting it All Together section later in this lesson.
Putting it All Together
For simplicity, we showed snippets of code in previous sections to demonstrate the applicable techniques. It is also useful to have an entire code listing to see how this code is used in a working program. Listing 1 shows all of the code used in this example, along with a driver in the Main method to produce formatted output.
Listing 1. SqlConnection Demo
using System; using System.Data; using System.Data.SqlClient;
///<summary>
/// Demonstrates how to work with SqlCommand objects ///</summary> class SqlCommandDemo
{
SqlConnection conn; public SqlCommandDemo()
{ // Instantiate the connection conn = new SqlConnection(
“Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI”); } // call methods that demo SqlCommand capabilities staticvoid Main() { SqlCommandDemo scd = new SqlCommandDemo();
Console.WriteLine(); Console.WriteLine(”Categories Before Insert”); Console.WriteLine(”————————”); // use ExecuteReader method scd.ReadData(); // use ExecuteNonQuery method for Insert scd.Insertdata(); Console.WriteLine(); Console.WriteLine(”Categories After Insert”); Console.WriteLine(”——————————”);
scd.ReadData();
// use ExecuteNonQuery method for Update scd.UpdateData();
Console.WriteLine(); Console.WriteLine(”Categories After Update”); Console.WriteLine(”——————————”);
scd.ReadData();
// use ExecuteNonQuery method for Delete scd.DeleteData();
Console.WriteLine(); Console.WriteLine(”Categories After Delete”); Console.WriteLine(”——————————”);
scd.ReadData();
// use ExecuteScalar method int numberOfRecords = scd.GetNumberOfRecords();
Console.WriteLine(); Console.WriteLine(”Number of Records: {0}”, numberOfRecords); }
/// <summary> /// use ExecuteReader method /// </summary> publicvoid ReadData() { SqlDataReader rdr = null; try { // Open the connection conn.Open(); // 1. Instantiate a new command with a query and connection SqlCommand cmd = new SqlCommand(”select CategoryName from Categories”, conn); // 2. Call Execute reader to get query results rdr = cmd.ExecuteReader();
// print the CategoryName of each record while (rdr.Read()) { Console.WriteLine(rdr[0]); } } finally { // close the reader if (rdr != null) { rdr.Close(); } // Close the connection if (conn != null) { conn.Close(); } } }
/// <summary> /// use ExecuteNonQuery method for Insert ///</summary> publicvoid Insertdata() { try { // Open the connection conn.Open(); // prepare command string string insertString = @” insert into Categories (CategoryName, Description) values (’Miscellaneous’, ‘Whatever doesn”t fit elsewhere’)”; // 1. Instantiate a new command with a query and connection SqlCommand cmd = new SqlCommand(insertString, conn); // 2. Call ExecuteNonQuery to send command cmd.ExecuteNonQuery(); } finally { // Close the connection if (conn != null) { conn.Close(); } } } /// <summary> /// use ExecuteNonQuery method for Update /// </summary> publicvoid UpdateData() { try { // Open the connection conn.Open(); // prepare command string string updateString = @” update Categories set CategoryName = ‘Other’ where CategoryName = ‘Miscellaneous’”; // 1. Instantiate a new command with command text only SqlCommand cmd = new SqlCommand(updateString);
// 2. Set the Connection property cmd.Connection = conn; // 3. Call ExecuteNonQuery to send command cmd.ExecuteNonQuery(); } finally { // Close the connection if (conn != null) { conn.Close(); } } } /// <summary> /// use ExecuteNonQuery method for Delete /// </summary> publicvoid DeleteData() { try { // Open the connection conn.Open(); // prepare command string string deleteString = @” delete from Categories where CategoryName = ‘Other’”; // 1. Instantiate a new command SqlCommand cmd = new SqlCommand(); // 2. Set the CommandText property cmd.CommandText = deleteString; // 3. Set the Connection property cmd.Connection = conn; // 4. Call ExecuteNonQuery to send command cmd.ExecuteNonQuery(); } finally { // Close the connection if (conn != null) { conn.Close(); } } }
/// <summary> /// use ExecuteScalar method /// </summary> /// <returns>number of records</returns> publicint GetNumberOfRecords() { int count = -1; try { // Open the connection conn.Open(); // 1. Instantiate a new command SqlCommand cmd = new SqlCommand(”select count(*) from Categories”, conn); // 2. Call ExecuteNonQuery to send command count = (int)cmd.ExecuteScalar(); } finally { // Close the connection if (conn != null) { conn.Close(); } } return count; }
}
In Listing 1, the SqlConnection object is instantiated in the SqlCommandDemo structure. This is okay because the object itself will be cleaned up when the CLR garbage collector executes. What is important is that we close the connection when we are done using it. This program opens the connection in a try block and closes it in a finally block in each method.
The ReadData method displays the contents of the CategoryName column of the Categories table. We use it several times in the Main method to show the current status of the Categories table, which changes after each of the insert, update, and delete commands. Because of this, it is convenient to reuse to show you the effects after each method call.
ADO.NET provides an API for accessing database systems according to the programs. ADO.NET was created for the .NET Framework and it can be said that they are next generation of Active Data Objects (ADO). The .NET Framework contains several namespaces and classes, which are devoted to database accesses. Microsoft has created separate namespaces that are optimized for working with different data providers (different types of databases). Before starting this topic, the user should have good knowledge of Databases such as MS Access and SQL Server or Oracle.
The following data provides specific namespaces that are included with ADO.NET:
System.Data.SqlClient
Contains classes for connecting to Microsoft SQL Server version 7.0 or higher
System.Data.OleDb
Contains classes for connecting to a data source that has an OLE DB provider
System.Data.Odbc
Contains classes for connecting to a data source that has an ODBC driver
System.Data.OracleClient
Contains classes for connecting to an Oracle database server
The System. Data. SqlClient namespace includes the following three classes:
* SqlConnection
* SqlCommand
* SqlDataReader
You will use these classes very often when you plan to build your ASP.NET application with Microsoft SQL Server (version 7.0 or higher). These classes enable you to execute SQL statements and retrieve data quickly from a database query.
The SqlConnection class represents an open connection to a Microsoft SQL Server database.
The SqlCommand class represents a SQL statement or stored procedure.
Finally, the SqlDataReader class represents the results from a database query.
The next section of this chapter goes into the details of using each of these classes.
The first group of classes, from the System.Data.SqlClient namespace, works only with Microsoft SQL Server. If you want to work with another type of database, such as an Access or Oracle database, you have to use the classes from one of the other data provider-specific namespaces.
For example, if you want to connect to a Microsoft Access database, then you’ll need to use the classes from the System. Data. OleDb namespace. The System.Data.OleDb namespace includes the following classes:
It has to be noted that these classes have the same names as the ones in the previous group, except that these class names start with OleDb rather than SQL.
The OleDbConnection class represents an open database connection to a database.
The OleDbCommand class represents a SQL statement or stored procedure.
The OleDbReader class represents the results from a database query.
Performing Common Database Tasks
In the following sections, the method of performing common database tasks using ADO.NET will be discussed. First, you will create and open a database connection.
Second, you will retrieve and display database records, add new database records, update existing database records, and delete database records.
Third, you will perform all these tasks using classes from both the System.Data.SqlClient and System.Data.OleDb namespaces.
When you work with SQL Server, you need to import the System.Data.SqlClient namespace by adding the following page directive at the top of your ASP.NET page:
<%@ Import Namespace=”System.Data.SqlClient” %>
When working with other databases, such as Microsoft Access or Oracle databases, you need to import the System.Data.OleDb namespace by using the following page directive:
<% @ Import Namespace=”System.Data.OleDb” %>
How to Open a Database Connection ?
The following Example would show how to create and open a connection for a Microsoft SQL Server databases.
Example 17 OpenSqlConnection.aspx
Firstly the System.Data.OleDb namespace is imported at the top of the page rather in the System. Data.SqlClient namespace.
Secondly,Instead of SQL classes OleDb classes are used in all the statements.
After you finish displaying the records from a DataReader, be sure to explicitly close it (using the Close () method).
Retrieving a Single Database Record
The developers generally need a single record from a database. Therefore it is very important to know how to retrieve it. For example, given a username you might have to look up someone’s password or retrieve a phone number for John from a database table.
Another common situation which requires retrieval of single database record is aggregate functions like MAX (), MIN (), COUNT () etc.
The previous section dealt with the use of a DataReader representing the results of a database query. But if there is a need, to retrieve a single result from a query, the ExecuteScalar () method is more efficient than the ExecuteReader () method, as it requires less coding.
The following example counts the no. of records from Employees table. We use the ExecuteScalar () method to retrieve and display the value of the SQL Count (*) aggregate function.
Example 21 SqlExecuteScalarCount.aspx
<%@ Import Namespace=”System.Data.SqlClient” %>
<Script Runat=”Server”>
Sub Page_Load
Dim conNorthwind As SqlConnection
Dim cmdSelectCount As SqlCommand
conNorthwind = New SqlConnection( “Server=localhost;UID=sa;PWD=secret;database=Northwind” )
conNorthwind.Open()
cmdSelectCount = New SqlCommand( “Select Count(*) From Employees”, conNorthwind )
