SqlDataAdapter

• DataTableのLoadメソッド

SqlConnectionStringBuilder bldr = new SqlConnectionStringBuilder();
bldr.DataSource = @".\SQLExpress";
bldr.InitialCatalog = "Test";
bldr.UserID = "sa";
bldr.Password= "sa";
bldr.Pooling= true;
string connectionString = bldr.ConnectionString;

using (SqlConnection con = new SqlConnection(conStriung))
{
    con.Open();

    using (SqlCommand cmd = con.CreateCommand())
    {
        cmd.CommandText = "SELECT ID, NAME FROM T_USER";
        DataTable dt = new DataTable("UserTable");

        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            dt.Load(rdr);

        }

        foreach (DataRow dr in dt.Rows)
        {
            Console.WriteLine(dr["ID"].ToString() + "/" + dr["NAME"].ToString());
        }
    }
}

• SqlDataAdapterのコンストラクタ1

SqlConnectionStringBuilder bldr = new SqlConnectionStringBuilder();
bldr.DataSource = @".\SQLExpress";
bldr.InitialCatalog = "Test";
bldr.UserID = "sa";
bldr.Password= "sa";
bldr.Pooling= true;
string connectionString = bldr.ConnectionString;

string selectSql = "SELECT ID, NAME FROM T_USER";
DataTable dt = new DataTable();

using (SqlDataAdapter da = new SqlDataAdapter(selectSql, connectionString))
{
    da.Fill(dt);
}

foreach (DataRow dr in dt.Rows)
{
    Console.WriteLine(dr["ID"].ToString() + "/" + dr["NAME"].ToString());
}

• SqlDataAdapterのコンストラクタ2

SqlConnectionStringBuilder bldr = new SqlConnectionStringBuilder();
bldr.DataSource = @".\SQLExpress";
bldr.InitialCatalog = "Test";
bldr.UserID = "sa";
bldr.Password= "sa";
bldr.Pooling= true;
string connectionString = bldr.ConnectionString;

string selectSql = "SELECT ID, NAME FROM T_USER";
DataTable dt = new DataTable();

using (SqlConnection con = new SqlConnection(connectionString))
{
    using (SqlDataAdapter da = new SqlDataAdapter(selectSql, con))
    {
        da.Fill(dt);
    }
}

foreach (DataRow dr in dt.Rows)
{
    Console.WriteLine(dr["ID"].ToString() + "/" + dr["NAME"].ToString());
}

• SqlDataAdapterのコンストラクタ3

SqlConnectionStringBuilder bldr = new SqlConnectionStringBuilder();
bldr.DataSource = @".\SQLExpress";
bldr.InitialCatalog = "Test";
bldr.UserID = "sa";
bldr.Password= "sa";
bldr.Pooling= true;
string connectionString = bldr.ConnectionString;

string selectSql = "SELECT ID, NAME FROM T_USER";
DataTable dt = new DataTable();

using (SqlConnection con = new SqlConnection(connectionString))
{
    using(SqlCommand cmd = new SqlCommand(selectSql,con))
    {
        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
            da.Fill(dt);    
        }
    }
}

foreach (DataRow dr in dt.Rows)
{
    Console.WriteLine(dr["ID"].ToString() + "/" + dr["NAME"].ToString());
}

• 複数の結果セットを取得

SqlConnectionStringBuilder bldr = new SqlConnectionStringBuilder();
bldr.DataSource = @".\SQLExpress";
bldr.InitialCatalog = "Test";
bldr.UserID = "sa";
bldr.Password= "sa";
bldr.Pooling= true;
string connectionString = bldr.ConnectionString;

string selectSql = "SELECT ID, NAME FROM T_USER; SELECT * FROM T_TABLE; SELECT * FROM T_TABLE;";

DataSet ds = new DataSet();

using (SqlConnection con = new SqlConnection(connectionString))
{
    using (SqlCommand cmd = new SqlCommand(selectSql, con))
    {
        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
            da.TableMappings.Add("Table", "T_TABLE1");
            da.TableMappings.Add("Table1", "T_TABLE2");
            da.TableMappings.Add("Table2", "T_TABLE3");

            da.Fill(ds);
        }
    }
}

for (int i = 0; i < ds.Tables.Count; i++)
{
    Console.WriteLine(ds.Tables[i].TableName);
    foreach (DataRow dr in ds.Tables[i].Rows)
    {
        Console.WriteLine(dr["ID"].ToString() + "/" + dr["NAME"].ToString());
    }
    
}

• SqlDataAdapter高速化

SqlConnectionStringBuilder bldr = new SqlConnectionStringBuilder();
bldr.DataSource = @".\SQLExpress";
bldr.InitialCatalog = "Test";
bldr.UserID = "sa";
bldr.Password= "sa";
bldr.Pooling= true;
string connectionString = bldr.ConnectionString;

string selectSql = "SELECT ID, NAME FROM T_USER";
DataSet ds = new DataSet();

using (SqlConnection con = new SqlConnection(connectionString))
{
    using (SqlCommand cmd = new SqlCommand(selectSql, con))
    {
        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
            ds.EnforceConstraints = false;
            da.Fill(ds,"T_USER");
            ds.EnforceConstraints = true;
        }
    }
}

foreach (DataRow dr in ds.Tables["T_USER"].Rows)
{
    Console.WriteLine(dr["ID"].ToString() + "/" + dr["NAME"].ToString());
}