Getting back a record set and fill out a datatable
-Gets a record set
-Use SqlDataAdapter to execute SQL command
/// <summary>
/// Uses a datatable so that you can sort the gridview
/// </summary>
/// <returns></returns>
public static DataTable GetAllCustomers()
{
DataTable dt;
string connectionString = @"Data Source=(local);Initial Catalog=AdventureWorksLT2008R2;Integrated Security=SSPI";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
//in-line sql
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "select * from SalesLT.Customer";
// For stored procedures
//SqlCommand command = connection.CreateCommand();
//command.CommandType = CommandType.StoredProcedure;
//command.CommandText = "sp_get_all_customers";
dt = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(dt);
}
return dt;
}
Getting back a record set and reading one record at a time
-Gets a record set
-Use SqlDataReader
/// <summary>
/// Uses a reader object to bind to the object properties
/// </summary>
/// <returns></returns>
public static List<Customer> GetAllCustomersList()
{
string connectionString = @"Data Source=(local);Initial Catalog=AdventureWorksLT2008R2;Integrated Security=SSPI";
List<Customer> Customers = new List<Customer>();
Customer cust;
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = @"select * from SalesLT.Customer";
command.CommandType = CommandType.Text; //CommandType.Text is used for SQL statements.
//if you are using a stored proc
//command.CommandType = CommandType.StoredProcedure;
//command.CommandText = "sp_get_all_customers";
connection.Open();
SqlDataReader reader = null;
reader = command.ExecuteReader();
// read one record at a time
while (reader.Read())
{
cust = new Customer();
cust.CustomerID = (int)reader["CustomerID"];
cust.Title = reader["Title"].ToString();
cust.Suffix = reader["Suffix"].ToString();
cust.FirstName = reader["FirstName"].ToString();
cust.MiddelName = reader["MiddleName"].ToString();
cust.LastName = reader["LastName"].ToString();
cust.CompanyName = reader["CompanyName"].ToString();
cust.SalesPerson = reader["SalesPerson"].ToString();
cust.EmailAddress = reader["EmailAddress"].ToString();
cust.PhoneNumber = reader["Phone"].ToString();
Customers.Add(cust);
}
}
return Customers;
}
Update, delete or insert into a database.
-No return value
-Use ExecuteNonQuery()
/// <summary>
/// Use ExecuteNonQuery method to insert a record
/// </summary>
/// <returns>number of records</returns>
public static void InsertdataToDb()
{
string connectionString = @"Data Source=(local);Initial Catalog=AdventureWorksLT2008R2;Integrated Security=SSPI";
int count = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = @"insert into Cars_Demo" + " " + "values ('Jeep', '1992')"; //use appropriate SQL statments
command.CommandType = CommandType.Text; //CommandType.Text is used for SQL statements.
connection.Open();
count = (int)command.ExecuteNonQuery(); //note ExecuteNonQuery is used!
}
}
This is how you retrieve a SINGLE value from the database. It has to be the first record, first column.
-Get a (known) single Record
-Use ExecuteScalar()
/// <summary>
/// Use ExecuteScalar method to get the first record, first column value from a query
/// </summary>
/// <returns>number of records</returns>
public static int GetNumberOfRecords_Demo()
{
string connectionString = @"Data Source=(local);Initial Catalog=AdventureWorksLT2008R2;Integrated Security=SSPI";
int count = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = connection.CreateCommand();
//in line sql
command.CommandText = @"select count(*) from Cars_Demo";
command.CommandType = CommandType.Text;
//using stored procedures
//command.CommandType = CommandType.StoredProcedure;
//command.CommandText = "myProc";
connection.Open();
count = (int)command.ExecuteScalar(); //note ExecuteScalar is used!
}
return count;
}
This is how you retrieve a OUTPUT PARAMETER value from a stored procedure.
-Get a (known) single Record back
-Use ExecuteReader() or ExecuteNonQuery()
/// <summary>
/// How to get a value back from a sql output parameter
/// </summary>
/// <returns>number of records</returns>
public static int CallaProcWithReturnValue()
{
string connectionString = @"Data Source=(local);Initial Catalog=AdventureWorksLT2008R2;Integrated Security=SSPI";
int numberOfHondas;
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_proc_w_output_parameter";
command.Parameters.Add(new SqlParameter("@numOfHondaCars", SqlDbType.Int));
command.Parameters["@numOfHondaCars"].Direction = ParameterDirection.Output;
connection.Open();
// if you are using ExecuteNonQuery()
command.ExecuteNonQuery();
connection.Close(); // Get output params AFTER we've processed and close connection
numberOfHondas = (int)command.Parameters["@numOfHondaCars"].Value;
// if you are using a reader
//SqlDataReader reader = null;
//reader = command.ExecuteReader();
//while(reader.Read())
//{
// //do something
//}
//reader.Close();
// Get output params AFTER we've processed
// CLOSED the SqlDataReadeer FIRST!
//numberOfHondas = (int)command.Parameters["@numOfHondaCars"].Value;
}
return numberOfHondas;
}
ExecuteReader : Use for accessing data. It provides a forward-only, read-only, connected recordset.
ExecuteNonQuery : Use for data manipulation, such as Insert, Update, Delete. You don’t expect a return value or a record set. (returns number of rows affected if proc does not have SET NOCOUNT ON.
ExecuteScalar : Use for retriving 1 row 1 col. value., i.e. Single value. eg: for retriving aggregate function. It is faster than other ways of retriving a single value from DB.
a developer's notes - a semi-technical web development BLOG 