SqlConnection conn = new SqlConnection("server=10.126.64.11;user=it_oper;pwd=IT@SME2018;database=MESOtheata;");
conn.Open();
SqlCommand cmd = new SqlCommand("select * from [A_Emp_Dept_20190522]", conn);//    或者
                                                                             //或者    SqlCommand cmd=conn.CreateCommand();cmd.CommandText="";
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);//讀取完畢自動關閉連接
for (int i = 0; i < reader.FieldCount - 1; i++)
{
    Console.WriteLine(reader.GetName(i));//獲取字段名
    while (reader.Read())//或者使用reader.HasRows屬性判斷時候有記錄
    {
        Console.WriteLine(reader.GetDateTime(0).ToString());
        //或者 Console.WriteLine(Convert.ToDateTime(reader[0]).ToString());
        object[] values = new object[reader.FieldCount - 1];
        reader.GetValues(values);//讀取一行所有字段的內(nèi)容到Values字段中
        if (reader.IsDBNull(0)
        {
            //判斷某字段是否為null值。
        }
    }

}
reader.Close();
//系統(tǒng)未關閉時，本Command更改CommandText后或新建Command還有繼續(xù)使用此連接。
//conn.Close();

SqlCommand cmd = new SqlCommand("select * from [A_Emp_Dept_20190522] where  empname=@empname", conn);
SqlParameter para = new SqlParameter("@empname", SqlDbType.NVarChar, 10);
para.Value = "曾亮";
cmd.Parameters.Add(para);
//或者直接 cmd.Parameters.AddWithValue("@empname","曾亮");

OleDbCommand cmd = new OleDbCommand("select * from [A_Emp_Dept_20190522] where  empname like ?  and line like ?", conn);
cmd.Parameters.AddWithValue("@empname", "曾亮");
cmd.Parameters.AddWithValue("@line", "ODMZ%");//參加的參數(shù)的順序應與？號的順序相同，參數(shù)名可隨意執(zhí)行，甚至可以是空串。

SqlCommand cmd = new SqlCommand("update  [A_Emp_Dept_20190522] set dept='aa' where  dept='' ", conn);
int RecordAffected = cmd.ExecuteNonQuery();
if (RecordAffected == 0)
{
    Console.Write("操作失敗");
}

SqlCommand cmd = new SqlCommand("select count(*) from   [A_Emp_Dept_20190522] ", conn);
if (Convert.ToInt32(cmd.ExecuteScalar()) == 0)//無記錄，返回null
{
    Console.Write("無記錄");
}

SqlTransaction trans = null;
try
{
    trans = conn.BeginTransaction();//默認事務隔離級別為ISolationLevel.Serilizable,Oracle為ISolationLevel.ReadCommited
    SqlCommand cmd = new SqlCommand("Insert int **", conn);
    cmd.Transaction = trans;//為命令指定一個事務
    cmd.ExecuteNonQuery();
    //可以有多個SqlCommand一并執(zhí)行，一起提交或回滾
    trans.Commit();
}
catch
{
    trans.Rollback();
}

SqlCommand cmd = new SqlCommand("GetCustomer", conn);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter para1 = new SqlParameter("@CustomerID", SqlDbType.NChar, 10);
para1.Value = "UINET";
cmd.Parameters.Add(para1);

SqlParameter para2 = new SqlParameter("@CompanyName", SqlDbType.NVarChar, 30);
para2.Direction = ParameterDirection.Output;
cmd.Parameters.Add(para2);

cmd.ExecuteNonQuery();
Console.WriteLine(cmd.Parameters["@CompanyName"].Value);

SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly);//僅僅讀取模式信息
DataTable table = reader.GetSchemaTable();
foreach (DataRow row in table.Rows)
{
    foreach (DataColumn col in table.Columns)
    {
        Console.WriteLine(col.ColumnName + row[col.ColumnName]);
    }
}

SqlCommand cmd = new SqlCommand("select * from Catogories;select * from Customers", conn);

SqlDataReader reader = cmd.ExecuteReader();//僅僅讀取模式信息
DataTable table = reader.GetSchemaTable();
do//首先自動定位在第一個結(jié)果集上
{
    while (reader.Read())
    {
        Console.Write(reader[0]);
    }
} while (reader.NextResult());