一 数据库连接方式
常用数据库为access,sqlserver,针对不同的数据库有不同的连接字符串,至于oracle,不同版本有不同的要求
access:
using System.Data.OleDb;
连接字符串:Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\\Northwind.mdb
\\Data Source=access数据库路径
sqlserver:
using System.Data.SqlClient;
连接字符串:Data Source=.;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=123
\\Data Source=服务器地址,如果是本机,可以用"."来代替;Initial Catalog=数据库名;User ID=用户名;Password=密码
oracle:
using System.Data.OracleClient; \\该引用需要专门添加,在winform下,点击项目-添加引用-System.Data.OracleClient;在网站下,点击网站-添加引用-System.Data.OracleClient
连接字符串:Data Source=.;user=admin;password=123;";
二 使用Command对象操作数据库中记录
- 通过TextBox控件的信息查询数据库中相应记录
页面源代码中添加一个GridView控件(GridView1),一个Button按钮(Button1),一个TextBox控件(txtName)
cs代码:
public SqlConnection GetConnection()
{
//读取web.config中的连接字符串,创建SqlConnection连接
string myStr = ConfigurationManager.AppSettings["ConnectionString"].ToString();
SqlConnection myConn = new SqlConnection(myStr);
return myConn;
}
protected void bind()
{
//此处为GridView1绑定数据库
SqlConnection myConn = GetConnection();
myConn.Open();
string sqlStr = "select * from test";
SqlDataAdapter myDa = new SqlDataAdapter(sqlStr, myConn);
DataSet myDs = new DataSet();
myDa.Fill(myDs);
GridView1.DataSource = myDs;
GridView1.DataBind();
//释放并关闭连接
myDa.Dispose();
myDs.Dispose();
myConn.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
//如果输入姓名不为空
if (this.txtName.Text != "")
{
SqlConnection myConn = GetConnection();
myConn.Open();
string sqlStr = "select * from test where Name=@Name";
SqlCommand myCmd = new SqlCommand(sqlStr, myConn);
//添加一个Parameters
myCmd.Parameters.Add("@Name", SqlDbType.VarChar, 20).Value = this.txtName.Text.Trim();
SqlDataAdapter myDa = new SqlDataAdapter(myCmd);
DataSet myDs = new DataSet();
myDa.Fill(myDs);
if (myDs.Tables[0].Rows.Count > 0)
{
GridView1.DataSource = myDs;
GridView1.DataBind();
}
else
{
Response.Write("<script>alert('没有相关记录')</script>");
}
myDa.Dispose();
myDs.Dispose();
myConn.Close();
}
else
{
this.bind();
}
}
- 通过TextBox控件的信息向数据库中添加数据
cs代码:
public SqlConnection GetConnection()
{
//读取web.config中的连接字符串,创建SqlConnection连接
string myStr = ConfigurationManager.AppSettings["ConnectionString"].ToString();
SqlConnection myConn = new SqlConnection(myStr);
return myConn;
}
protected void bind()
{
//此处为GridView1绑定数据库
SqlConnection myConn = GetConnection();
myConn.Open();
string sqlStr = "select * from test";
SqlDataAdapter myDa = new SqlDataAdapter(sqlStr, myConn);
DataSet myDs = new DataSet();
myDa.Fill(myDs);
GridView1.DataSource = myDs;
GridView1.DataBind();
//释放并关闭连接
myDa.Dispose();
myDs.Dispose();
myConn.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
//如果输入姓名不为空
if (this.txtName.Text != "")
{
SqlConnection myConn = GetConnection();
myConn.Open();
string sqlStr = "insert into test(Name) value ('"
+ this.txtName.Text.Trim() + "')";
SqlCommand myCmd = new SqlCommand(sqlStr, myConn);
//对连接执行语句,并返回影响的行数
myCmd.ExecuteNonQuery();
myCmd.Dispose();
myConn.Close();
this.bind();
}
else
{
this.bind();
}
}
- 通过GridView1中的编辑按钮编辑数据库中数据
cs代码:
public SqlConnection GetConnection()
{
//读取web.config中的连接字符串,创建SqlConnection连接
string myStr = ConfigurationManager.AppSettings["ConnectionString"].ToString();
SqlConnection myConn = new SqlConnection(myStr);
return myConn;
}
protected void bind()
{
//此处为GridView1绑定数据库
SqlConnection myConn = GetConnection();
myConn.Open();
string sqlStr = "select * from test";
SqlDataAdapter myDa = new SqlDataAdapter(sqlStr, myConn);
DataSet myDs = new DataSet();
myDa.Fill(myDs);
GridView1.DataSource = myDs;
//设置绑定数据表的主键值为"ID"字段
GridView1.DataKeyNames = new string[] { "ID" };
GridView1.DataBind();
//释放并关闭连接
myDa.Dispose();
myDs.Dispose();
myConn.Close();
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
//如果点击编辑按钮,则获取要编辑的行索引
GridView1.EditIndex = e.NewEditIndex;
this.bind();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
//提交编辑时,先取得要编辑行的主键值
int ID = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString());
//获取同行中第2列目前数据值
string Name = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[2].Controls[0])).Text.Trim();
string sqlStr = "update test set Name='" + Name + "' where ID=" + ID;
SqlConnection myConn = GetConnection();
myConn.Open();
SqlCommand myCmd = new SqlCommand(sqlStr, myConn);
myCmd.ExecuteNonQuery();
myCmd.Dispose();
myConn.Close();
//编辑完毕后,将编辑行索引=-1
GridView1.EditIndex = -1;
this.bind();
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
//退出编辑后,将编辑行索引=-1
GridView1.EditIndex = -1;
this.bind();
}
- 通过GridView1中的删除按钮删除数据库中数据
页面源代码中添加一个GridView控件(GridView1),设置AutoGenerateDeleteButton="True"。并设置GridView控件的RowDeleting,RowDataBound事件。
cs代码:
public SqlConnection GetConnection()
{
//读取web.config中的连接字符串,创建SqlConnection连接
string myStr = ConfigurationManager.AppSettings["ConnectionString"].ToString();
SqlConnection myConn = new SqlConnection(myStr);
return myConn;
}
protected void bind()
{
//此处为GridView1绑定数据库
SqlConnection myConn = GetConnection();
myConn.Open();
string sqlStr = "select * from test";
SqlDataAdapter myDa = new SqlDataAdapter(sqlStr, myConn);
DataSet myDs = new DataSet();
myDa.Fill(myDs);
GridView1.DataSource = myDs;
//设置绑定数据表的主键值为"ID"字段
GridView1.DataKeyNames = new string[] { "ID" };
GridView1.DataBind();
//释放并关闭连接
myDa.Dispose();
myDs.Dispose();
myConn.Close();
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
//提交编辑时,先取得要编辑行的主键值
int ID = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString());
string sqlStr = "delete from test where ID="+ID;
SqlConnection myConn = GetConnection();
myConn.Open();
SqlCommand myCmd = new SqlCommand(sqlStr, myConn);
myCmd.ExecuteNonQuery();
myCmd.Dispose();
myConn.Close();
//编辑完毕后,将编辑行索引=-1
GridView1.EditIndex = -1;
this.bind();
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
//点击删除按钮后弹出对话框
if (e.Row.RowType == DataControlRowType.DataRow)
{
((LinkButton)e.Row.Cells[0].Controls[0]).Attributes.Add("onclick", "return confirm('确定要删除吗?')");
}
}
- 实现数据库事务处理,如果操作错误则进行事务回滚
页面源代码中添加一个GridView控件(GridView1),一个Button按钮(Button1),一个TextBox控件(txtName)。
cs代码:
public SqlConnection GetConnection()
{
//读取web.config中的连接字符串,创建SqlConnection连接
string myStr = ConfigurationManager.AppSettings["ConnectionString"].ToString();
SqlConnection myConn = new SqlConnection(myStr);
return myConn;
}
protected void bind()
{
//此处为GridView1绑定数据库
SqlConnection myConn = GetConnection();
myConn.Open();
string sqlStr = "select * from test";
SqlDataAdapter myDa = new SqlDataAdapter(sqlStr, myConn);
DataSet myDs = new DataSet();
myDa.Fill(myDs);
GridView1.DataSource = myDs;
//设置绑定数据表的主键值为"ID"字段
GridView1.DataKeyNames = new string[] { "ID" };
GridView1.DataBind();
//释放并关闭连接
myDa.Dispose();
myDs.Dispose();
myConn.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection myConn = GetConnection();
myConn.Open();
string sqlStr = "insert into test(Name) value ('"
+ this.txtName.Text.Trim() + "')";
//此处添加一个sql事务,并开始数据库事务。
//警告: 如果启动了某个事务,并且服务器上出现了 16 级或更高级别的错误,则在调用 Read 方法以前,该事务不会回滚。ExecuteReader 上不会引发异常。
SqlTransaction sqlTrans=myConn.BeginTransaction();
SqlCommand myCmd = new SqlCommand(sqlStr, myConn);
//设置在myCmd中执行事务。
myCmd.Transaction=sqlTrans;
try
{
myCmd.ExecuteNonQuery();
//提交事务,事务一旦提交就不能回滚。也就是说这里表示正确执行,不再需要事务回滚。
sqlTrans.Commit();
myCmd.Clone();
this.bind();
}
catch
{
Response.Write("<script>alert('插入失败,执行事务回滚')</script>");
//事务回滚。
sqlTrans.Rollback();
}
}