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

October 28, 2011

How to Uninstall Sql Server Express that gets installed with Visual Studio 2010

Filed under: SQL — Duy Nguyen @ 1:12 pm
Tags: , , , , , , , ,

1. Go to Add/ Remove Programs.
2. Choose “Microsoft SQL Server 2008”
**I had Microsoft SQL Server 2008 R2 installed but you still need to select “Microsoft SQL Server 2008” NOT “Microsoft SQL Server 2008 R2”. If you do choose R2, you will see the SQLEXPRESS instance but you will not be able to select it!

3. Click on the REMOVE link (This will allow you to chose the instance to remove. See image)

4. Select SQLEXPRESS and remove!

September 5, 2011

How to search all columns of all tables in a sql database for a keyword

Filed under: SQL — Duy Nguyen @ 2:26 pm
Tags: , , , , , ,

How to search all columns of all tables in a database for a keyword? Check out this great blog entry: http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

You can also use these queries to find which table a particular column name resides in:

SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name = ‘MYCOLUMNNAME’ )

SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name like ‘%PART_OF_NAME%’ )

How to find SQL Instances installed and uninstall SQL Server 2005 Express

Filed under: General Tips — Duy Nguyen @ 2:17 pm
Tags: , , , , , ,

Run this script in Management Studio to find what instances are installed:

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/03/20/how-to-list-all-the-installed-sql-server-on-the-server-using-tsql.aspx

1. In add/remove find Microsoft SQL Server 2005 Express
2. Uninstall
3. Choose Microsoft SQL Server 2005 Express instances
4. If prompted to do so, stop any Microsoft SQL Server 2005 Express services in services.msc

Install order for Visual Studio and SQL Server

Filed under: General Tips — Duy Nguyen @ 1:06 pm
Tags: , , , , , ,

Install SQL Server 2005 BEFORE you install Visual Studio 2005/2008. If you install Visual Studio first, you will have a hard time installing SQL Server because Visual Studio , by default, will install SQL Server Express. And when you try to install SQL Server on top of an existing Express installation, you won’t be able to install SQL Server Management Studio.

Create a free website or blog at WordPress.com.