使用DataRelation類創建關系并利用父/子關系讀取數據示例

void?Page_Load(object?sender,?System.EventArgs?e)

????????????
{
????????????????//?連接字符串和?SQL?語句
????????????????string?ConnString?=?System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"];
????????????????string?Sql?=?"SELECT?*?FROM?Customers;?SELECT?*?FROM?Orders";

????????????????//?創建?Connection?和?DataAdapter?對象
????????????????SqlConnection?myConn?=?new?SqlConnection(ConnString);
????????????????SqlDataAdapter?sqlAdapter?=?new?SqlDataAdapter(Sql,?myConn);

????????????????//?填充數據
????????????????DataSet?dataSet?=?new?DataSet();
????????????????sqlAdapter.Fill(dataSet,?"Table");

????????????????//?命名表名
????????????????dataSet.Tables[0].TableName?=?"Customers";
????????????????dataSet.Tables[1].TableName?=?"Orders";
????????????
????????????????//?創建?Customers?和?Orders?的父/子表關系
????????????????dataSet.Relations.Add("CustomersOrders",?dataSet.Tables["Customers"].Columns["CustomerID"],
????????????????????dataSet.Tables["Orders"].Columns["CustomerID"]);

????????????????//?使用?GetChildRows()?方法遍歷子行
????????????????foreach(DataRow?custRow?in?dataSet.Tables["Customers"].Rows)

????????????????
{
????????????????????myLabel.Text?+=?"<b>Parent?Row:?"?+?custRow["CustomerID"]?+?" "?+?custRow["CompanyName"]?+?"</b><br>";
????????????????????myLabel.Text?+=?"Child?Row:?<br>";
????????????????????foreach(DataRow?orderRow?in?custRow.GetChildRows("CustomersOrders"))

????????????????????
{
????????????????????????myLabel.Text?+=?" ?"?+?orderRow["OrderID"]?+?" "?+?orderRow["EmployeeID"]?+?"<br>";
????????????????????}
????????????????}
????????????}



?
將DataSet的改動更新回SQL Server數據庫

????//?連接字符串及?SQL?語句
????????????????string?ConnString?=?System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"];
????????????????string?Sql?=?"SELECT?CustomerID,CompanyName,Country?FROM?Customers";

????????????????//?連接?SqlConnection?對象,并和?SqlDataAdapter?關聯
????????????????SqlConnection?thisConnection?=?new?SqlConnection(ConnString);
????????????????SqlDataAdapter?adapter?=?new?SqlDataAdapter(Sql,?thisConnection);

????????????????//?創建?DataSet?對象
????????????????DataSet?data?=?new?DataSet();

????????????????//?創建?SqlCommandBuilder?對象,并和?SqlDataAdapter?關聯
????????????????SqlCommandBuilder?builder?=?new?SqlCommandBuilder(adapter);
????????????????adapter.Fill(data,?"Customers");

????????????????//?修改?DataSet?的內容
????????????????data.Tables["Customers"].Rows[0]["CompanyName"]?=?"CompanyName1";
????????????????data.Tables["Customers"].Rows[0]["Country"]?=?"AAAA";
????????????????data.Tables["Customers"].Rows[1]["CompanyName"]?=?"CompanyName2";
????????????????data.Tables["Customers"].Rows[1]["Country"]?=?"BBBB";

????????????????//?在?DataSet?中新增行
????????????????DataRow?newRow?=?data.Tables["Customers"].NewRow();
????????????????newRow["CustomerID"]?=?"New";
????????????????newRow["CompanyName"]?=?"New?CompanyName";
????????????????newRow["Country"]?=?"New?Country";
????????????????data.Tables["Customers"].Rows.Add(newRow);

????????????????//?從?DataSet?更新?SQL?Server?數據庫
????????????????adapter.Update(data,?"Customers");
????????????}

在 ASP.NET 使用存儲過程示例
//?連接字符串
????????????string?ConnStr?=?System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"];

????????????//?創建Connection對象
????????????SqlConnection?myConn?=?new?SqlConnection(ConnStr);

????????????//?創建Command對象并和Connection對象關聯
????????????SqlCommand?myCommand?=?new?SqlCommand();
????????????myCommand.Connection?=?myConn;
????????????
????????????//?指定要執行的存儲過程名稱
????????????myCommand.CommandText?=?"CustomersProc";
????????????//?使用要執行的是存儲過程
????????????myCommand.CommandType?=?CommandType.StoredProcedure;

??????????????????????????????????執行帶參數的存儲過程
????????????????????????//{
????????????????//?創建SqlParameter對象,指定參數名稱、數據類型、長度及參數值
????????????????//SqlParameter?para?=?new?SqlParameter("@country",?SqlDbType.NVarChar,?15);
????????????????//para.Value?=?value;
????????????????
????????????????//myCommand.Parameters.Add(para);}

????????????//?創建DataAdapter對象填充數據
????????????DataSet?myDS?=?new?DataSet();
????????????SqlDataAdapter?adapter?=?new?SqlDataAdapter(myCommand);
????????????adapter.Fill(myDS,?"Customers");

????????????//?將返回的數據和DataGrid綁定顯示
????????????myDataGrid.DataSource?=?myDS.Tables["Customers"];
????????????myDataGrid.DataBind();
????????}

輸出參數的
CREATE?PROCEDURE?EmployeesProc?
????@TitleOfCourtesy?nvarchar(25),
????@empCount?int?OUTPUT?
AS

SELECT?EmployeeID,LastName,FirstName,Title,TitleOfCourtesy
????FROM?Employees?WHERE?TitleOfCourtesy=@TitleOfCourtesy

SELECT?@empCount?=?COUNT(*)?
????FROM?Employees?WHERE?TitleOfCourtesy=@TitleOfCourtesy
GO


//?創建?Connection?和?Command?對象
????????????????SqlConnection?myConn?=?new?SqlConnection(ConnStr);
????????????????SqlCommand?myCommand?=?new?SqlCommand("EmployeesProc",?myConn);

????????????????//?指定要執行的命令為存儲過程
????????????????myCommand.CommandType?=?CommandType.StoredProcedure;

????????????????//?增加輸入參數并賦值
????????????????myCommand.Parameters.Add("@TitleOfCourtesy",?SqlDbType.NVarChar,?20);
????????????????myCommand.Parameters["@TitleOfCourtesy"].Value?=?myDropDownList.SelectedItem.Text;
????????????????myCommand.Parameters["@TitleOfCourtesy"].Direction?=?ParameterDirection.Input;

????????????????//?增加輸出參數
????????????????myCommand.Parameters.Add("@empCount",?SqlDbType.Int);
????????????????myCommand.Parameters["@empCount"].Direction?=?ParameterDirection.Output;

????????????????//?創建?DataAdapter?對象填充數據
????????????????DataSet?myDS?=?new?DataSet();
????????????????SqlDataAdapter?adapter?=?new?SqlDataAdapter(myCommand);
????????????????adapter.Fill(myDS,?"Customers");

獲得數據庫中表的數目和名稱
string?ConnStr?=?System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"];
????????????????string?listQuery?=?"SELECT?name?FROM?sysobjects?WHERE?xtype?=?'U'";
????????????????string?sumQuery?=?"SELECT?COUNT(*)?FROM?sysobjects?WHERE?xtype?=?'U'";

????????????????SqlCommand?myCommand?=?new?SqlCommand();
????????????????myCommand.Connection?=?new?SqlConnection(ConnStr);

????????????????myCommand.Connection.Open();

????????????????//?獲得用戶表的數目
????????????????myCommand.CommandText?=?sumQuery;
????????????????SumLabel.Text?=?myCommand.ExecuteScalar().ToString();

????????????????//?獲得用戶表的列表
????????????????myCommand.CommandText?=?listQuery;
????????????????SqlDataReader?myReader?=?myCommand.ExecuteReader();
????????????????ListLabel.Text?=?"";
????????????????while(myReader.Read())

????????????????
{
????????????????????ListLabel.Text?+=?"<br> "?+?myReader[0].ToString();
????????????????}
????????????????myReader.Close();
????????????
????????????????myCommand.Connection.Close();????


獲取服務器端數據庫列表示例

//?創建連接及執行數據庫操作
????????????????string?db_query?=?"sp_helpdb";
????????????????SqlCommand?myCommand?=?new?SqlCommand(db_query,?new?SqlConnection(ConnStr));
????????????????myCommand.Connection.Open();
????????????????SqlDataReader?dr?=?myCommand.ExecuteReader();

????????????????//?將數據庫列表綁定到下拉列表控件(DropDownList)
????????????????DropDownList1.DataSource?=?dr;
????????????????DropDownList1.DataTextField?=?"name";
????????????????DropDownList1.DataBind();

????????????????//?關閉DataReader對象和數據庫連接
????????????????dr.Close();
????????????????myCommand.Connection.Close();

保存圖片到SQL Server數據庫示例
將數據庫保存的圖片顯示到頁面上示例
CREATE?TABLE?[dbo].[ImageTable]?(
????[ImageID]?[int]?IDENTITY?(1,?1)?NOT?NULL?,
????[ImageData]?[image]?NULL?,
????[ImageContentType]?[varchar]?(50)?COLLATE?Chinese_PRC_CI_AS?NULL?,
????[ImageDescription]?[varchar]?(200)?COLLATE?Chinese_PRC_CI_AS?NULL?,
????[ImageSize]?[int]?NULL?
)?ON?[PRIMARY]?TEXTIMAGE_ON?[PRIMARY]
GO



????void?Button_Submit(System.Object?sender,?System.EventArgs?e)?

????????????
{
??????????????//?HttpPostedFile對象,用于讀取圖象文件屬性
??????????????HttpPostedFile?UpFile?=?UP_FILE.PostedFile;

??????????????//?FileLength?變量存儲圖片的字節大小
??????????????int?FileLength?=?UpFile.ContentLength;

??????????????try

??????????????
{
????????????????if?(FileLength?==?0)

????????????????
{
????????????????????txtMessage.Text?=?"<b>您未選擇上傳的文件</b>";
????????????????}
????????????????else

????????????????
{
????????????????????//?創建存儲圖片文件的臨時?Byte?數組
????????????????????Byte[]?FileByteArray?=?new?Byte[FileLength];

????????????????????//?建立數據流對象
????????????????????Stream?StreamObject?=?UpFile.InputStream;??
????
????????????????????//?讀取圖象文件數據,FileByteArray為數據儲存體,0為數據指針位置、FileLnegth為數據長度
????????????????????StreamObject.Read(FileByteArray,0,FileLength);???

????????????????????//?數據庫操作
????????????????????string?ConnStr?=?System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"];
????????????????????string?query?=?"INSERT?INTO?ImageTable?(ImageData,?ImageContentType,?ImageDescription,?ImageSize)?VALUES?(@ImageData,?@ImageContentType,?@ImageDescription,?@ImageSize)";
????????????????????SqlCommand?myCommand?=?new?SqlCommand(query,?new?SqlConnection(ConnStr));

????????????????????//?添加各項參數并賦值
????????????????????myCommand.Parameters.Add("@ImageData",?SqlDbType.Image);
????????????????????myCommand.Parameters.Add("@ImageContentType",?SqlDbType.VarChar,?50);
????????????????????myCommand.Parameters.Add("@ImageDescription",?SqlDbType.VarChar,?200);
????????????????????myCommand.Parameters.Add("@ImageSize",?SqlDbType.BigInt);
????????????????????myCommand.Parameters["@ImageData"].Value?=?FileByteArray;
????????????????????myCommand.Parameters["@ImageContentType"].Value?=?UpFile.ContentType;
????????????????????myCommand.Parameters["@ImageDescription"].Value?=?txtDescription.Text;
????????????????????myCommand.Parameters["@ImageSize"].Value?=?FileLength;
????????????????????
????????????????????//?執行數據庫操作
????????????????????myCommand.Connection.Open();
????????????????????myCommand.ExecuteNonQuery();
????????????????????myCommand.Connection.Close();

????????????????????//?提示上傳成功
????????????????????txtMessage.Text?=?"<b>上傳文件成功</b>";
????????????????}
????????????}?
????????????catch?(Exception?ex)?

????????????
{
????????????????//?使用?Label?標簽顯示異常
????????????????txtMessage.Text?=?ex.Message.ToString();
????????????}
????????}
????????????

void?SubmitBtn_Click(object?sender,?System.EventArgs?e)

????????????
{
????????????????int?ImgID?=?Convert.ToInt32(ImgIDTextBox.Text);

????????????????string?ConnStr?=?System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"];
????????????????string?query?=?"SELECT?*?FROM?ImageTable?WHERE?ImageID?=?@ImageID";

????????????????SqlCommand?myCommand?=?new?SqlCommand(query,?new?SqlConnection(ConnStr));
????????????????myCommand.Parameters.Add("@ImageID",?SqlDbType.Int);
????????????????myCommand.Parameters["@ImageID"].Value?=?ImgID;
????????????????myCommand.Connection.Open();

????????????????SqlDataReader?dr?=?myCommand.ExecuteReader();

????????????????if(dr.Read())

????????????????
{
????????????????????Response.ContentType?=?(string)dr["ImageContentType"];
????????????????????Response.OutputStream.Write((byte[])dr["ImageData"],?0,?(int)dr["ImageSize"]);
????????????????}
????????????????else

????????????????
{
????????????????????Response.Write("沒有這個圖片的ID號");
????????????????????Response.End();
????????????????}

????????????????dr.Close();
????????????????myCommand.Connection.Close();
????????????}

獲得插入記錄標識號的示例

????????????void?Page_Load(object?sender,?System.EventArgs?e)

????????????
{
????????????????//?數據庫連接字符串
????????????????string?ConnStr?=?System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"];
????????????????//?創建插入SQL語句及調用@@identity函數返回標識值
????????????????string?insert_query?=?"insert?into?Categories?(CategoryName,Description)?values?('IT',?'Internet');"
????????????????????????+?"SELECT?@@identity?AS?'identity';";

????????????????//?執行數據庫操作
????????????????SqlCommand?myCommand?=?new?SqlCommand(insert_query,?new?SqlConnection(ConnStr));
????????????????myCommand.Connection.Open();
????????????????myLabel.Text?=?myCommand.ExecuteScalar().ToString();
????????????????myCommand.Connection.Close();
????????????}


如何讀取Excel表格中的數據
void?SubmitBtn_Click(object?sender,?System.EventArgs?e)

????????????
{????
????????????????//?獲取Excep文件的完整路徑
????????????????string?source?=?File1.Value;

????????????????string?ConnStr?=?"Provider=Microsoft.Jet.OLEDB.4.0;Data?Source="?+?source?+?";Extended?Properties=Excel?8.0";
????????????????string?query?=?"SELECT?*?FROM?[Sheet1$]";

????????????????OleDbCommand?oleCommand?=?new?OleDbCommand(query,?new?OleDbConnection(ConnStr));
????????????????OleDbDataAdapter?oleAdapter?=?new?OleDbDataAdapter(oleCommand);
????????????????DataSet?myDataSet?=?new?DataSet();

????????????????//?將?Excel?的[Sheet1]表內容填充到?DataSet?對象
????????????????oleAdapter.Fill(myDataSet,?"[Sheet1$]");

????????????????//?數據綁定
????????????????DataGrid1.DataSource?=?myDataSet;
????????????????DataGrid1.DataMember?=?"[Sheet1$]";
????????????????DataGrid1.DataBind();
????????????}

<form?id="Form1"?method="post"?runat="server">
????????????<H3>如何讀取Excel表格中的數據</H3>
????????????請選擇Excel表格:<BR>
????????????<INPUT?type="file"?id="File1"?name="File1"?runat="server"?size="26"><br>
????????????<asp:Button?
????????????????id="SubmitBtn"?
????????????????runat="server"?
????????????????Text="開始顯示"?
????????????????OnClick="SubmitBtn_Click">
????????????</asp:Button><br>
????????????<br>
????????????<asp:DataGrid?id="DataGrid1"?runat="server"></asp:DataGrid>
????????</form>//備份數據庫例
<%?@Import?Namespace="System.Data"?%>
<%?@Import?Namespace="System.Data.SqlClient"?%>
<HTML>
????<HEAD>
????????<title>獲取服務器端數據庫列表示例</title>
????????<script?language="C#"?runat="server">
????????
????????????//?注意本節的數據庫連接字符串
????????????string?ConnStr?=?System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer1"];
????????????
????????????void?Page_Load(object?sender,?System.EventArgs?e)

????????????
{
????????????????if(!IsPostBack)

????????????????
{
????????????????????//?創建連接及執行數據庫操作
????????????????????string?db_query?=?"sp_helpdb";

????????????????????SqlCommand?myCommand?=?new?SqlCommand(db_query,?new?SqlConnection(ConnStr));
????????????????????myCommand.Connection.Open();
????????????????????SqlDataReader?dr?=?myCommand.ExecuteReader();

????????????????????//?將數據庫列表綁定到下拉列表控件(DropDownList)
????????????????????dbDropDownList.DataSource?=?dr;
????????????????????dbDropDownList.DataTextField?=?"name";
????????????????????dbDropDownList.DataBind();

????????????????????//關閉DataReader對象和數據庫連接
????????????????????dr.Close();
????????????????????myCommand.Connection.Close();
????????????????}
????????????}
????????????
????????????void?dbDropDownList_SelectedIndexChanged(object?sender,?System.EventArgs?e)

????????????
{
????????????????pathTextBox.Text?=?@"C:\BACKUP\"?+?dbDropDownList.SelectedValue?+?".bak";
????????????}
????????????
????????????void?backupButton_Click(object?sender,?System.EventArgs?e)

????????????
{
????????????????string?path?=?pathTextBox.Text;
????????????????string?dbname?=?dbDropDownList.SelectedValue;

????????????????string?backupSql?=?"use?master;";
????????????????backupSql?+=?"backup?database?@dbname?to?disk?=?@path;";
????????????
????????????????SqlCommand?myCommand?=?new?SqlCommand(backupSql,?new?SqlConnection(ConnStr));

????????????????myCommand.Parameters.Add("@dbname",?SqlDbType.Char);
????????????????myCommand.Parameters["@dbname"].Value?=?dbname;
????????????????myCommand.Parameters.Add("@path",?SqlDbType.Char);
????????????????myCommand.Parameters["@path"].Value?=?path;

????????????????try

????????????????
{
????????????????????myCommand.Connection.Open();
????????????????????myCommand.ExecuteNonQuery();
????????????????????infoLabel.Text?=?"備份成功";
????????????????}
????????????????catch(Exception?ex)

????????????????
{
????????????????????infoLabel.Text?=?"備份失敗<br>"?+?ex.ToString();
????????????????}
????????????????finally

????????????????
{
????????????????????myCommand.Connection.Close();
????????????????}
????????????}
????????????
????????????void?restoreButton_Click(object?sender,?System.EventArgs?e)

????????????
{
????????????????string?path?=?pathTextBox.Text;
????????????????string?dbname?=?dbDropDownList.SelectedValue;

????????????????string?restoreSql?=?"use?master;";
????????????????restoreSql?+=?"restore?database?@dbname?from?disk?=?@path;";

????????????????SqlCommand?myCommand?=?new?SqlCommand(restoreSql,?new?SqlConnection(ConnStr));

????????????????myCommand.Parameters.Add("@dbname",?SqlDbType.Char);
????????????????myCommand.Parameters["@dbname"].Value?=?dbname;
????????????????myCommand.Parameters.Add("@path",?SqlDbType.Char);
????????????????myCommand.Parameters["@path"].Value?=?path;

????????????????try

????????????????
{
????????????????????myCommand.Connection.Open();
????????????????????myCommand.ExecuteNonQuery();
????????????????????infoLabel.Text?=?"恢復成功";
????????????????}
????????????????catch(Exception?ex)

????????????????
{
????????????????????infoLabel.Text?=?"恢復失敗<br>"?+?ex.ToString();
????????????????}
????????????????finally

????????????????
{
????????????????????myCommand.Connection.Close();
????????????????}
????????????}
????????
????????</script>
????</HEAD>
????<body>
????????<form?id="Form1"?method="post"?runat="server">
????????????<h3>獲取服務器端數據庫列表示例</h3>
????????????????數據庫列表:
????????????????<asp:dropdownlist?id="dbDropDownList"?runat="server"?AutoPostBack="True"?
????????????????????OnSelectedIndexChanged="dbDropDownList_SelectedIndexChanged"></asp:dropdownlist>
????????????????<br><br>
????????????????請輸入備份目錄及備份文件名:
????????????????<asp:textbox?id="pathTextBox"?runat="server"?Width="224px">
????????????????????C:\BACKUP\Northwind.bak</asp:textbox>(目錄必須存在)
????????????????<br><br>
????????????????<asp:button?id="backupButton"?runat="server"?Font-Size="9pt"?Text="備份數據庫"?
????????????????????OnClick="backupButton_Click"></asp:button>
????????????????<asp:button?id="restoreButton"?runat="server"?Font-Size="9pt"?Text="恢復數據庫"?
????????????????????OnClick="restoreButton_Click"></asp:button>
????????????????<br><br>
????????????????<asp:Label?id="infoLabel"?runat="server"></asp:Label>
????????</form>
????</body>
</HTML>
