a developer's notes – a semi-technical web development BLOG

January 26, 2012

C# ADO.NET Examples and Variations

Filed under: ASP.NET,C# — Duy Nguyen @ 11:20 pm
Tags: , , , , , , , ,

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.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: