My C# Cookbook...

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