asp.net操作数据库相关[导入] [转载]

asp.net操作数据库相关[导入]

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

SELECT EmployeeID,LastName,FirstName,Title,TitleOfCourtesy

FROM Employees WHERE TitleOfCourtesy=@TitleOfCourtesy

SELECT @empCount = COUNT(*)

FROM Employees WHERE TitleOfCourtesy=@TitleOfCourtesy

// 创建 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]

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();

}

<H3>如何读取Excel表格中的数据</H3>

请选择Excel表格：<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>

// 注意本节的数据库连接字符串

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>

<h3>获取服务器端数据库列表示例</h3>

数据库列表：

<asp:dropdownlist id="dbDropDownList" runat="server" AutoPostBack="True"

OnSelectedIndexChanged="dbDropDownList_SelectedIndexChanged"></asp:dropdownlist>

请输入备份目录及备份文件名：

<asp:textbox id="pathTextBox" runat="server" Width="224px">

C:\BACKUP\Northwind.bak</asp:textbox>（目录必须存在）

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

<asp:Label id="infoLabel" runat="server"></asp:Label>

</form>

</body>

</HTML>

posted on 2008-05-22 16:42 鱼跃于渊阅读(323) 评论(0) 编辑收藏举报

刷新页面返回顶部

鱼跃于渊

asp.net操作数据库相关[导入] [转载]

asp.net操作数据库相关[导入]

导航

公告