My C# Cookbook...

一时失言乱红尘 2021-12-15 08:59 198阅读 0赞

##### ADO Connection   using System;   using System.Data;   using System.Data.SqlClient;      ///<summary>   /// Demonstrates how to work with SqlConnection objects   ///</summary>   class SqlConnectionDemo   \{       static void Main()       \{           // 1. Instantiate the connection           SqlConnection conn = new SqlConnection(               "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");              SqlDataReader rdr = null;              try           \{               // 2. Open the connection               conn.Open();                  // 3. Pass the connection to a command object               SqlCommand cmd = new SqlCommand("select \* from Customers", conn);                  //               // 4. Use the connection               //                  // get query results               rdr = cmd.ExecuteReader();                  // print the CustomerID of each record               while (rdr.Read())               \{                   Console.WriteLine(rdr\[0\]);               \}           \}           finally           \{               // close the reader               if (rdr != null)               \{                   rdr.Close();               \}                  // 5. Close the connection               if (conn != null)               \{                   conn.Close();               \}           \}       \}   \}      Command CRUD   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        static void 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>        public void 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>        public void 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>        public void 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>        public void 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>        public int 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;        \}    \}      DataReader   using System;   using System.Data;   using System.Data.SqlClient;      namespace Lesson04   \{   class ReaderDemo   \{   static void Main()   \{   ReaderDemo rd = new ReaderDemo();   rd.SimpleRead();   \}      public void SimpleRead()   \{   // declare the SqlDataReader, which is used in   // both the try block and the finally block   SqlDataReader rdr = null;      // create a connection object   SqlConnection conn = new SqlConnection(   "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");      // create a command object   SqlCommand cmd  = new SqlCommand(   "select \* from Customers", conn);      try   \{   // open the connection   conn.Open();      // 1.  get an instance of the SqlDataReader   rdr = cmd.ExecuteReader();      // print a set of column headers   Console.WriteLine(   "Contact Name             City                Company Name");   Console.WriteLine(   "------------             ------------        ------------");      // 2.  print necessary columns of each record   while (rdr.Read())   \{   // get the results of each column   string contact = (string)rdr\["ContactName"\];   string company = (string)rdr\["CompanyName"\];   string city    = (string)rdr\["City"\];      // print out the results   Console.Write("\{0,-25\}", contact);   Console.Write("\{0,-20\}", city);   Console.Write("\{0,-25\}", company);   Console.WriteLine();   \}   \}   finally   \{   // 3. close the reader   if (rdr != null)   \{   rdr.Close();   \}      // close the connection   if (conn != null)   \{   conn.Close();   \}   \}   \}   \}   \}      DataSet   using System;   using System.Data;   using System.Data.SqlClient;   using System.Drawing;   using System.Windows.Forms;      class DisconnectedDataForm : Form   \{   private SqlConnection  conn;   private SqlDataAdapter daCustomers;      private DataSet  dsCustomers;   private DataGrid dgCustomers;      private const string TableName = "Customers";      // initialize form with DataGrid and Button   public DisconnectedDataForm()   \{   // fill dataset   InitData();      // set up datagrid   dgCustomers = new DataGrid();   dgCustomers.Location = new Point(5, 5);   dgCustomers.Size = new Size(   this.ClientRectangle.Size.Width - 10,   this.ClientRectangle.Height - 50);   dgCustomers.DataSource = dsCustomers;   dgCustomers.DataMember = TableName;      // create update button   Button btnUpdate = new Button();   btnUpdate.Text = "Update";   btnUpdate.Location = new Point(   this.ClientRectangle.Width/2 - btnUpdate.Width/2,   this.ClientRectangle.Height - (btnUpdate.Height + 10));   btnUpdate.Click += new EventHandler(btnUpdateClicked);      // make sure controls appear on form   Controls.AddRange(new Control\[\] \{ dgCustomers, btnUpdate \});   \}      // set up ADO.NET objects   public void InitData()   \{   // instantiate the connection   conn = new SqlConnection(   "Server=(local);DataBase=Northwind;Integrated Security=SSPI");      // 1. instantiate a new DataSet   dsCustomers = new DataSet();      // 2. init SqlDataAdapter with select command and connection   daCustomers = new SqlDataAdapter(   "select CustomerID, CompanyName from Customers", conn);      // 3. fill in insert, update, and delete commands   SqlCommandBuilder cmdBldr = new SqlCommandBuilder(daCustomers);      // 4. fill the dataset   daCustomers.Fill(dsCustomers, TableName);   \}      // Update button was clicked   public void btnUpdateClicked(object sender, EventArgs e)   \{   // write changes back to DataBase   daCustomers.Update(dsCustomers, TableName);   \}      // start the Windows Form   static void Main()   \{   Application.Run(new DisconnectedDataForm());   \}   \}      Adding Parameters to Queries   using System;   using System.Data;   using System.Data.SqlClient;      class ParamDemo   \{   static void Main()   \{   // conn and reader declared outside try   // block for visibility in finally block   SqlConnection conn   = null;   SqlDataReader reader = null;      string inputCity = "London";      try   \{   // instantiate and open connection   conn =  new   SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");   conn.Open();      // don't ever do this!   // SqlCommand cmd = new SqlCommand(   // "select \* from Customers where city = '" + inputCity + "'";      // 1. declare command object with parameter   SqlCommand cmd = new SqlCommand(   "select \* from Customers where city = @City", conn);      // 2. define parameters used in command object   SqlParameter param  = new SqlParameter();   param.ParameterName = "@City";   param.Value         = inputCity;      // 3. add new parameter to command object   cmd.Parameters.Add(param);      // get data stream   reader = cmd.ExecuteReader();      // write each record   while(reader.Read())   \{   Console.WriteLine("\{0\}, \{1\}",   reader\["CompanyName"\],   reader\["ContactName"\]);   \}   \}   finally   \{   // close reader   if (reader != null)   \{   reader.Close();   \}      // close connection   if (conn != null)   \{   conn.Close();   \}   \}   \}   \}         Transaction   http://www.informit.com/articles/article.aspx?p=29843&seqNum=3   Working With ADO.NET Transactions      'Create Connection         Dim conn as SqlConnection = new SqlConnection("Data Source=" + \_                "localhost;Initial Catalog=northwind;UID=sa;PWD=;")            'Create Command         Dim cmd as SqlCommand = new SqlCommand()         cmd.Connection = conn            'Connection must be open to start transaction         conn.Open()            'Create Transaction and apply it to command object         Dim myTrans = conn.BeginTransaction("TransactionName")         cmd.Transaction = myTrans            'Show Database before modifications         GetAndBindData(datagrid1)            'Execute Database Change \#1         ExecuteSQL(cmd, "UPDATE Fruits SET Quantity = 4 " + \_                 "WHERE Name = 'Apple'")            'Execute Database Change \#2         Try           ExecuteSQL(cmd, "DELETE FROM FRUITS")           Throw New Exception("A random horrible database error")           myTrans.Commit()         Catch           myTrans.Rollback()         End Try            'Requery to make sure changes are gone         GetAndBindData(datagrid7)            conn.Close()    #####

转载于:https://www.cnblogs.com/yangbin990/archive/2005/09/13/236277.html