Using ADO.net to execute SQL queries

Programming SQL Server 2012 Ch10-1

 //Insert Customer using Direct T-SQL Statement
        private void btnDirectSql_Click(object sender, EventArgs e)
        {
            const string ConnStr =
             // "Data Source=localhost;Initial Catalog=SampleDb;Integrated Security=true;";
            "Data Source=(localdb)\\Projects;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;Initial Catalog=SampleDb;";

            const string TSql =
              "INSERT INTO Customer (FirstName, LastName) VALUES ('Lukas', 'Keller')";

            using (var conn = new SqlConnection())
            {
                conn.ConnectionString = ConnStr;
                conn.Open();
                using (var cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = TSql;
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

Programming SQL Server 2012 Ch10-2

 //Insert Customer using Parameterized T-SQL Statement
        private void btnParameterizedDirectSql_Click(object sender, EventArgs e)
        {
            const string ConnStr =
            "Data Source=(localdb)\\Projects;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;Initial Catalog=SampleDb;";

            const string TSql =
              "INSERT INTO Customer (FirstName, LastName) VALUES (@FirstName, @LastName)";

            using (var conn = new SqlConnection())
            {
                conn.ConnectionString = ConnStr;
                conn.Open();
                using (var cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = TSql;              
                    cmd.Parameters.AddWithValue("@FirstName", this.txtFirstName.Text);
                    cmd.Parameters.AddWithValue("@LastName", this.txtLastName.Text);
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

Programming SQL Server 2012 Ch10-3

//Insert Customer using Stored Procedure
        private void btnStoredProcedure_Click(object sender, EventArgs e)
        {
            const string ConnStr =
            "Data Source=(localdb)\\Projects;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;Initial Catalog=SampleDb;";

            const string StoredProcName = "InsertCustomer";

            using (var conn = new SqlConnection())
            {
                conn.ConnectionString = ConnStr;
                conn.Open();
                using (var cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = StoredProcName;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@FirstName", this.txtFirstName.Text);
                    cmd.Parameters.AddWithValue("@LastName", this.txtLastName.Text);
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

Programming SQL Server 2012 Ch10-4

 //Insert Customer using Stored Procedure and read inserted row using DataReader
        private void btnStoredProcedureINSERTwithReader_Click(object sender, EventArgs e)
        {
            const string ConnStr =
           "Data Source=(localdb)\\Projects;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;Initial Catalog=SampleDb;";

            const string StoredProcName = "InsertCustomer";

            using (var conn = new SqlConnection())
            {
                conn.ConnectionString = ConnStr;
                conn.Open();
                using (var cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = StoredProcName;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@FirstName", this.txtFirstName.Text);
                    cmd.Parameters.AddWithValue("@LastName", this.txtLastName.Text);
                    using(var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        rdr.Read();
                        var customerId = rdr.GetInt64(0);
                        var createdAt = rdr.GetDateTime(1);
                        var updatedAt = rdr.GetDateTime(2);
                        rdr.Close();
                        MessageBox.Show(string.Format("Customer ID {0}; Created at {1}; Updated at {2}", customerId, createdAt, updatedAt));
                    }
                }
                conn.Close();
            }

        }

Programming SQL Server 2012 Ch10-5

        //Select all Customers using DataReader
        private void btnStoredProcedureWithReader_Click(object sender, EventArgs e)
        {
            const string ConnStr =
"Data Source=(localdb)\\Projects;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;Initial Catalog=SampleDb;";

            const string StoredProcName = "SelectCustomers";
            var names = new List<string>();

            using (var conn = new SqlConnection())
            {
                conn.ConnectionString = ConnStr;
                conn.Open();
                using (var cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = StoredProcName;
                    cmd.CommandType = CommandType.StoredProcedure;
                    using (var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (rdr.Read())
                        {
                            var name = string.Format("{0},{1}",
                                rdr["LastName"], rdr["FirstName"]);
                            names.Add(name);
                        }
                        rdr.Close();
                    }
                }      
            }
            this.lstNames.DataSource = names;

        }

Programming SQL Server 2012 Ch10-6

        //Return scalar value using output parameter
        private void btnOutputParameter_Click(object sender, EventArgs e)
        {
            const string ConnStr =
"Data Source=(localdb)\\Projects;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;Initial Catalog=SampleDb;";

            const string StoredProcName = "GetCustomerBalance";
            var customerId = Convert.ToInt64(this.txtCustomerId.Text);
            decimal balance;
            
            using (var conn = new SqlConnection())
            {
                conn.ConnectionString = ConnStr;
                conn.Open();
                using (var cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = StoredProcName;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@CustomerId", customerId);
                    var outputParam = new SqlParameter()
                    {
                        ParameterName = "@Balance",
                        SqlDbType = SqlDbType.Money,
                        Direction = ParameterDirection.Output
                    };
                    cmd.Parameters.Add(outputParam);
                    cmd.ExecuteNonQuery();
                    balance = (decimal)outputParam.Value;

                }
                conn.Close();
            }
            MessageBox.Show(string.Format("Customer ID {0} balance is {1:c}", customerId, balance));
          

        }