DataSet对象中SQl的操作

 

DataSet用来对数据库进行操作，如果直接读取，使用ADO.NET的DataReader即可；

 

显示数据表的操作：

protected void Page_Load(object sender, EventArgs e)
{
//DataSet在从数据源中获取数据后就断开了与数据源的连接，即DataSet可以看成一个新的数据库，可以进行操作，然后还可以吧数据再送回数据源
//DataTable即数据表，用来存储数据。行DataRow、列DataColumn
SqlConnection myConnection = new SqlConnection();
String sqlconn = "Server=localhost;DataBase=Literature;Integrated Security=SSPI";
myConnection.ConnectionString = sqlconn;
myConnection.Open();
SqlCommand myCommand = new SqlCommand("select * from Works", myConnection);//SqlCommand用于对sql执行命令

SqlDataAdapter Adapter = new SqlDataAdapter();//DataAdapte检索、操作数据
Adapter.SelectCommand = myCommand;//DataAdapte检索
System.Data.DataSet myDs = new System.Data.DataSet();
Adapter.Fill(myDs);
Response.Write("<h3>使用DataTable</h3><hr>");
Response.Write("<table border=1 cellspacing=0 cellpadding=2>");
DataTable myTable = myDs.Tables[0];//返回DataSet数据集中的第一个数据表
Response.Write("<tr bgcolor=#DAB4B4>");
foreach(DataColumn myColumn in myTable.Columns)
{
Response.Write("<td>"+myColumn.ColumnName+"</td>");
}
foreach(DataRow myRow in myTable.Rows)
{
Response.Write("<tr>");
foreach(DataColumn myColumn in myTable.Columns)
{
Response.Write("<td>"+myRow[myColumn]+"</td>");
}
Response.Write("</tr>");
}
Response.Write("</table>");
myConnection.Close();
}

插入操作：

protected void Page_Load(object sender, EventArgs e)
{
SqlConnection myConnection = new SqlConnection();
String sqlconn = "Server=localhost;DataBase=Literature;Integrated Security=SSPI";
myConnection.ConnectionString = sqlconn;
myConnection.Open();
SqlCommand myCommand = new SqlCommand("select * from Works", myConnection);//SqlCommand用于对sql执行命令
SqlCommand sqlInsertCommand1 = new SqlCommand();
sqlInsertCommand1.CommandText = @"INSERT INTO Works(编号,名称,作者,类型) VALUES(@no,@name,@author,@type);";
sqlInsertCommand1.Connection = myConnection;
sqlInsertCommand1.Parameters.Add(new SqlParameter("@no", System.Data.SqlDbType.Int, 4, "编号"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@name", System.Data.SqlDbType.Text, 50, "名称"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@author", System.Data.SqlDbType.NChar, 10, "作者"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@type", System.Data.SqlDbType.NChar, 10, "类型"));
SqlDataAdapter Adapter = new SqlDataAdapter();
Adapter.SelectCommand = myCommand;
Adapter.SelectCommand = sqlInsertCommand1;
System.Data.DataSet myDs = new System.Data.DataSet();
Adapter.Fill(myDs);
DataTable myTable = myDs.Tables[0];
DataRow myRow = myTable.NewRow();
myRow["编号"] = 6546;
myRow["名称"] = "语文";
myRow["作者"] = "杜甫";
myRow["类型"] = "作文";
myTable.Rows.Add(myRow);//把新建的myRow添加到row中
Adapter.Update(myDs);//把数据集中的内容更新到数据库
myDs.Clear();
myConnection.Close();
myConnection.Open();
Adapter.Fill(myDs);
Response.Write("<h3>使用DataTable</h3><hr>");
Response.Write("<table border=1 cellspacing=0 cellpadding=2>");

Response.Write("<tr bgcolor=#DAB4B4>");
foreach (DataColumn myColumn in myTable.Columns)
{
Response.Write("<td>" + myColumn.ColumnName + "</td>");
}
foreach (DataRow Row in myTable.Rows)
{
Response.Write("<tr>");
foreach (DataColumn myColumn in myTable.Columns)
{
Response.Write("<td>" + Row[myColumn] + "</td>");
}
Response.Write("</tr>");
}
Response.Write("</table>");
}