使用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>