無連接模式：可以在沒有打開連接時在內存中操作數據，DataAdapter通過管理連接為無連接模式提供服務，當要從數據庫中查詢數據時，DataAdapter打開一個連接，填充指定的DataSet,等數據讀取完馬上自動關閉連接，然後可以對數據做修改，再次使用DataAdapter打開連接，持久化修改(無論是更新，刪除或是更新)，最後自動關閉連接，使用無連接模式的情況是有一些獨立數據，它們不會發生改變或者很少改變，因為在將填充DataSet和更新數據這段時間內數據庫中的實際數據也許會發生改變，如果需要將數據立即持久化到數據庫請使用連接模式
讀取數據到DataSet:無連接意味著一個連接建立了一個與數據庫的會話，請求的數據讀入到DataSet中，然後通過斷開數據庫的連接關閉會話，這時會話因為與數據庫的斷開而關閉，DataSet成為一個無連接的數據庫
復制代碼 代碼如下:
/// <summary>
/// 查詢學生信息
/// </summary>
/// <returns>返回填充了學生表的DataSet</returns>
public DataSet GetUserInfor()
{
string str = "Data Source=.;Initial Catalog=Student;Integrated Security=True";
var conn = new SqlConnection(str);
DataSet ds = new DataSet();
var sda = new SqlDataAdapter("SELECT * FROM Student",conn);
sda.Fill(ds, "student");//調用fill方法時，SqlDataAdapter會自動打開連接，讀取數據然後關閉連接
foreach (DataRow dr in ds.Tables["student"].Rows)
{
Console.WriteLine(dr["name"]);
}
return ds;
}
將DataSet的修改保存到數據庫
插入數據
復制代碼 代碼如下:
/// <summary>
/// 插入學生信息，並返回插入後的DataSet
/// </summary>
/// <param name="stu">學生實體類</param>
public DataSet InsertStudnt(Student stu)
{
DataSet ds = GetUserInfor();
string str = "Data Source=.;Initial Catalog=Student;Integrated Security=True";
string sql = "INSERT INTO student VALUES (@name,@age)";
var conn = new SqlConnection(str);
var cmd = new SqlCommand(sql,conn);
var sda = new SqlDataAdapter();
SqlParameter sqlParam1 = new SqlParameter()
{
ParameterName = "@name",
SourceColumn = "name"
};
SqlParameter sqlParam2 = new SqlParameter()
{
ParameterName = "@age",
SourceColumn = "age"
};
SqlParameter[] sqlParamArray = new SqlParameter[] {sqlParam1,sqlParam2 };
cmd.Parameters.AddRange(sqlParamArray);
sda.InsertCommand = cmd;
DataRow dr = ds.Tables["student"].NewRow();
dr["name"] = stu.name;
dr["age"] = stu.age;
ds.Tables["student"].Rows.Add(dr);
sda.Update(ds,"student");
return ds;
}
更新數據
復制代碼 代碼如下:
/// <summary>
/// 根據ID更新姓名和年齡
/// </summary>
/// <param name="name">姓名</param>
/// <param name="age">年齡</param>
/// <param name="id">學生ID</param>
/// <returns>返回更新後的DataSet</returns>
public DataSet UpdateStudent(Student stu,int id)
{
DataSet ds = GetUserInfor();
string str = "Data Source=.;Initial Catalog=Student;Integrated Security=True";
string sql = "UPDATE student SET name=@name,age=@age WHERE id=@id";
var conn = new SqlConnection(str);
var cmd = new SqlCommand(sql, conn);
var sda = new SqlDataAdapter();
SqlParameter param1 = new SqlParameter()
{
ParameterName="@name",SourceColumn="name"
};
SqlParameter param2 = new SqlParameter()
{
ParameterName = "@age",
SourceColumn = "age",
SqlDbType=SqlDbType.Int
};
SqlParameter param3 = new SqlParameter()
{
ParameterName = "@id",
SourceColumn = "id"
};
SqlParameter[] param = new SqlParameter[] {param1,param2,param3 };
cmd.Parameters.AddRange(param);
sda.UpdateCommand = cmd;
DataTable dt = ds.Tables["student"];
foreach (DataRow dr in dt.Rows)
{
int oldID=Convert.ToInt32(dr["id"]);
if (oldID == id)
{
dr["name"] = stu.name;
dr["age"] = stu.age;
}
}
sda.Update(ds,"student");
return ds;
}
刪除數據
復制代碼 代碼如下:
/// <summary>
/// 根據ID刪除一個學生
/// </summary>
/// <param name="id">返回更新後的DataSet</param>
public DataSet DeleteStudent(int id)
{
DataSet ds = GetUserInfor();
string str = "Data Source=.;Initial Catalog=Student;Integrated Security=True";
string sql = "DELETE FROM student WHERE id=@id";
var conn = new SqlConnection(str);
var cmd = new SqlCommand(sql, conn);
var sda = new SqlDataAdapter();
SqlParameter param = new SqlParameter()
{
ParameterName="@id",SourceColumn="id",SqlDbType=SqlDbType.Int
};
cmd.Parameters.Add(param);
sda.DeleteCommand = cmd;
DataTable dt=ds.Tables["student"];
foreach (DataRow dr in dt.Rows)
{
int oldId = Convert.ToInt32(dr["id"]);
if (oldId == id)
dr.Delete();
}
sda.Update(ds,"student");
return ds;
}
