【ADO.NET】Copy paste note

這篇只是用來貼常用到的一些 ADO.NET 語法

Adapter 寫 DataTable

string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["key"].ConnectionString;
using (SqlConnection sqlConn = new SqlConnection(connStr))
{
    sqlConn.Open();
    using (SqlCommand cmd = new SqlCommand(queryStr, sqlConn))
    {
        cmd.Parameters.AddWithValue("paramX", variableX);
        cmd.Parameters.AddWithValue("paramY", variableY);
        cmd.Parameters.Add("@paramZ", SqlDbType.VarChar, 50).Value = "something";
        DataTable dt = new DataTable();
        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
        {
            adapter.Fill(dt);
        }
    }
}

DataReader

using (SqlConnection sqlConn = new SqlConnection(connStr))
{
    sqlConn.Open();
    using (SqlCommand cmd = new SqlCommand(queryStr, sqlConn))
    {
        cmd.Parameters.AddWithValue("paramsA", valA);
        using (SqlDataReader dr = cmd.ExecuteReader())
        {
            // while (dr.Read()) for loop
            if (dr.Read())
            {
                string cellValue = dr.GetString(dr.GetOrdinal("ColumnName"));
                string cellValue2 = dr["ColumnName2"].ToString();
            }
        }
    }
}

Stored Procedure

using (SqlConnection sqlConn = new SqlConnection(CONN_STR))
{
    sqlConn.Open();
    using (SqlCommand cmd = new SqlCommand("usp_xyz", sqlConn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("x", x);
        
        DataTable dt = new DataTable();
        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
        {
            adapter.Fill(dt);
        }
    }
}

TransactionScope & Update

using (TransactionScope scope = new TransactionScope())
{                
    string connStr = @"...";
    using (SqlConnection sqlConn = new SqlConnection(connStr))
    {
        sqlConn.Open();
        string queryStr = @"UPDATE Table SET ColC = @colC 
                 WHERE ColA = @colA AND ColB = @colB ";
        using (SqlCommand cmd = new SqlCommand(queryStr, sqlConn))
        {
            foreach (var item in items)
            {
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("colA", item.A);
                cmd.Parameters.AddWithValue("colB", item.B);
                cmd.Parameters.Add("colC", SqlDbType.BigInt).Value = item.C;
                cmd.Parameters.Add(new SqlParameter() 
                { 
                    SqlDbType = SqlDbType.VarChar, 
                    Size = 18, 
                    ParameterName = "@colD", 
                    Value = item.D
                });
                int effectRowCount = cmd.ExecuteNonQuery();
            }
        }
    }
    scope.Complete();
}

Connection string builder

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder["Data Source"] = "ServerName";
builder["Integrated Security"] = true;
builder["Initial Catalog"] = "DbName";

//Data Source=.;Initial Catalog=DbName;Integrated Security=True

DataTable dt = new DataTable();
using (SqlConnection sqlConn = new SqlConnection(builder.ConnectionString))
{
    sqlConn.Open();
    using (SqlCommand cmd = new SqlCommand("SELECT TOP 1000 * FROM TheTable", sqlConn))
    {
        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
        {
            adapter.Fill(dt);
        }
    }
}