ado一部分程序.docx
- 文档编号:15338696
- 上传时间:2023-07-03
- 格式:DOCX
- 页数:21
- 大小:18.75KB
ado一部分程序.docx
《ado一部分程序.docx》由会员分享,可在线阅读,更多相关《ado一部分程序.docx(21页珍藏版)》请在冰点文库上搜索。
ado一部分程序
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Text;
usingSystem.Data;
usingSystem.Data.SqlClient;
namespaceADODemo2
{
publicclassSQLHelper
{
///
///用来获取连接字符串
///
publicstringMyConstr
{
get
{
//return"DataSource=PC201301221117;InitialCatalog=ADODB;UserID=sa;Pwd=sasa";
returnSystem.Configuration.ConfigurationManager.ConnectionStrings["MyconnString"].ConnectionString.ToString();
}
}
///
///创建于数据库连接。
///
///
publicstringCreateSQLConnection()
{
SqlConnectionmyCon=newSqlConnection(MyConstr);
myCon.Open();
strings=myCon.State.ToString();
returns;
}
///
///基于sql命令的数据库操作
///
///
///
publicintExecuteSQLReturnInt(stringsql)
{
SqlConnectioncon1=newSqlConnection(MyConstr);
//打开连接状态。
try
{
if(con1.State==ConnectionState.Closed||con1.State==ConnectionState.Broken)
{
con1.Open();
}
//SqlCommandcmd=newSqlCommand();
//cmd.Connection=con1;
//cmd.CommandText=sql;
SqlCommandcmd=newSqlCommand(sql,con1);
inti=cmd.ExecuteNonQuery();
returni;
}
catch
{
return0;
}
finally
{
con1.Close();
}
}
///
///基于带参数的sql命令的数据库操作。
///
///
///
///
publicintExecuteSQLReturnInt(stringsql,SqlParameter[]pars)
{
SqlConnectioncon1=newSqlConnection(MyConstr);
//打开练接状态。
try
{
if(con1.State==ConnectionState.Closed||con1.State==ConnectionState.Broken)
{
con1.Open();
}
//SqlCommandcmd=newSqlCommand();
//cmd.Connection=con1;
//cmd.CommandText=sql;
SqlCommandcmd=newSqlCommand(sql,con1);
#region给sql语句添加参数,参数传递给cmd的Parameters属性里
foreach(SqlParameterpinpars)
{
cmd.Parameters.Add(p);
}
#endregion
inti=cmd.ExecuteNonQuery();
returni;
}
catch
{
return0;
}
finally
{
con1.Close();
}
}
///
///返回结果集的数据库查询方法
///
///
///
publicDataSetSelectSqlReturnDataSet(stringsql)
{
SqlConnectionconn=newSqlConnection(MyConstr);
SqlDataAdaptersda=newSqlDataAdapter(sql,conn);
DataSetds=newDataSet();
sda.Fill(ds);
returnds;
}
publicSqlDataReaderSelectSQLRreturnReader(stringsql)
{
SqlConnectioncon=newSqlConnection(MyConstr);
if(con.State==ConnectionState.Broken||con.State==ConnectionState.Closed)
{
con.Open();
}
SqlCommandcmd=newSqlCommand(sql,con);
SqlDataReadersdr=cmd.ExecuteReader();
returnsdr;
}
///
///基于带参数的SQL命令的DataAdapter操作
///
///
///
publicDataSetSelectSqlReturnDataSet(stringsql,SqlParameter[]pars,CommandTypetype)
{
SqlConnectionconn=newSqlConnection(MyConstr);
SqlDataAdaptersda=newSqlDataAdapter(sql,conn);
if(pars!
=null&&pars.Length>0)
{
foreach(SqlParameterpinpars)
{
sda.SelectCommand.Parameters.Add(p);
}
}
sda.SelectCommand.CommandType=type;
DataSetds=newDataSet();
sda.Fill(ds);
returnds;
}
///
///基于sql命令或者存储过程,查询数据库操作
///
///
///
publicSqlDataReaderSelectSqlReturnDataReader(stringsql,SqlParameter[]pars,CommandTypetype)
{
SqlConnectioncon=newSqlConnection(MyConstr);
if(con.State==ConnectionState.Closed||con.State==ConnectionState.Broken)
{
con.Open();
}
SqlCommandcmd=newSqlCommand(sql,con);
if(pars!
=null||pars.Length>0)
{
foreach(SqlParameterpinpars)
{
cmd.Parameters.Add(p);
}
}
cmd.CommandType=type;
SqlDataReaderreader=cmd.ExecuteReader(CommandBehavior.CloseConnection);
returnreader;
}
publicDataTableExecuteSQLReturnDatatable(stringsql)
{
SqlConnectioncon=newSqlConnection(MyConstr);
SqlDataAdaptersda=newSqlDataAdapter(sql,con);
DataSetds=newDataSet();
sda.Fill(ds,"myTable");
returnds.Tables["myTable"];
}
publicDataTableExecuteSQLReturnDatatable(stringsql,SqlParameter[]pars,CommandTypetype)
{
SqlConnectioncon=newSqlConnection(MyConstr);
SqlDataAdaptersda=newSqlDataAdapter(sql,con);
DataSetds=newDataSet();
if(pars!
=null&&pars.Length>0)
{
foreach(SqlParameterpinpars)
{
sda.SelectCommand.Parameters.Add(p);
}
}
if(type!
=null)
{
sda.SelectCommand.CommandType=type;
}
sda.Fill(ds,"myTable");
returnds.Tables["myTable"];
}
}
}
Form1.cs
usingSystem.Data.SqlClient;
namespaceADODemo2
{
publicpartialclassForm1:
Form
{
SQLHelpersh=newSQLHelper();
publicForm1()
{
InitializeComponent();
}
privatevoidForm1_Load(objectsender,EventArgse)
{
label1.Text=sh.CreateSQLConnection();
stringsql="select*fromusers";
SqlDataReadersdr=sh.SelectSQLRreturnReader(sql);
sdr.Read();
this.label3.Text=sdr.GetString(0).ToString();
this.label4.Text=sdr.GetString
(1).ToString();
}
privatevoidbutton1_Click(objectsender,EventArgse)
{
stringsql="insertintousersvalues('赵云','456')";
inti=sh.ExecuteSQLReturnInt(sql);
this.label2.Text=i.ToString();
}
privatevoidbtnQuery_Click(objectsender,EventArgse)
{
stringsql="select*fromusers";
DataSetds=sh.SelectSqlReturnDataSet(sql);
DataTabledb=ds.Tables[0];
this.dataGridView1.DataSource=db;
}
privatevoidlabel4_Click(objectsender,EventArgse)
{
}
}
}
2014.4
UserManager.cs
usingSystem;
usingSystem.Collections.Generic;
usingSystem.ComponentModel;
usingSystem.Data;
usingSystem.Drawing;
usingSystem.Text;
usingSystem.Windows.Forms;
usingSystem.Data.SqlClient;
namespaceADODemo
{
publicpartialclassUserManager:
Form
{
publicUserManager()
{
InitializeComponent();
}
SQLhelperhelper=newSQLhelper();
privatevoidgbUserManager_Enter(objectsender,EventArgse)
{
}
///
///绑定相应的控件,DateGridViewcbDepartment
///
///
///
privatevoidUserManager_Load(objectsender,EventArgse)
{
userBind();
stringsql2="selectDepartmentID,DepartmentNamefromtbDepartment";
cbDepartment.DataSource=helper.executeSqlReturnDatatable(sql2);
cbDepartment.DisplayMember="DepartmentName";
cbDepartment.ValueMember="DepartmentID";
}
privatevoiduserBind()
{
stringsql1="selectIdas'编号',UserNameas'用户名称',Birthdayas'生日','部门名称'=(selectDepartmentNamefromtbDepartmentwheretbUser.DepartmentId=tbDepartment.DepartmentId)fromtbUser";
dataGridView1.DataSource=helper.executeSqlReturnDatatable(sql1);
}
privatevoidgbUserDisplay_Enter(objectsender,EventArgse)
{
}
///
///添加用户
///
///
///
privatevoidbtAdd_Click(objectsender,EventArgse)
{
stringsql="insertintotbUser(Username,Birthday,DepartmentId)values(@username,@birthday,@departmentid)";
SqlParameter[]pars=newSqlParameter[]
{
newSqlParameter("@username",this.txtUserName.Text),
newSqlParameter("@birthday",this.txtBirthday.Text),
newSqlParameter("@departmentid",this.cbDepartment.SelectedValue.ToString())
};
intcount=helper.executeSqlReturnInt(sql,pars);
if(count!
=0)
{
userBind();
MessageBox.Show("添加成功");
}
else
{
MessageBox.Show("添加失败");
}
}
///
///删除一条记录的事件,双击DATAGRIDVIEW控件
///
///
///
privatevoiddataGridView1_DoubleClick(objectsender,EventArgse)
{
if(MessageBox.Show("是否删除当前记录","删除记录",MessageBoxButtons.OKCancel)==DialogResult.OK)
{
stringid=this.dataGridView1.CurrentRow.Cells[0].Value.ToString();
stringsql="deletefromtbUserwhereId="+id;
intcount=helper.executeSqlReturnInt(sql);
if(count!
=0)
{
userBind();
MessageBox.Show("删除成功");
}
else
{
MessageBox.Show("删除失败");
}
}
}
privatevoiddataGridView1_Click(objectsender,EventArgse)
{
stringid=this.dataGridView1.CurrentRow.Cells[0].Value.ToString();
stringsql="select*fromtbUserwhereId="+id;
SqlDataReadersdr=helper.executeSqlReturnDataReader(sql);
if(sdr.Read())
{
this.txtUserName.Text=sdr["UserName"].ToString();
this.txtBirthday.Text=Convert.ToDateTime(sdr["Birthday"]).ToShortDateString();
this.cbDepartment.SelectedValue=sdr["DepartmentId"].ToString();
}
}
privatevoidbutton1_Click(objectsender,EventArgse)
{
stringid=this.dataGridView1.CurrentRow.Cells[0].Value.ToString();
if(MessageBox.Show("是否真的修改id为"+id+"的当前记录","修改记录",MessageBoxButtons.OKCancel)==DialogResult.OK)
{
stringsql="updatetbUsersetUserName=@username,Birthday=@birthday,DepartmentId=@departmentidwhereId=@id";
SqlParameter[]pars=newSqlParameter[]
{
newSqlParameter("@username",this.txtUserName.Text),
newSqlParameter("@birthday",this.txtBirthday.Text),
newSqlParameter("@departmentid",this.cbDepartment.SelectedValue.ToString()),
newSqlParameter("@id",id)
};
intcount=helper.executeSqlReturnInt(sql,pars);
if(count!
=0)
{
userBind();
MessageBox.Show("修改成功");
}
else
{
MessageBox.Show("修改失败");
}
}
}
}
}
数据库操作类SQLhelper
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Text;
usingSystem.Data.SqlClient;
usingSystem.Data;
namespaceADODemo
{
classSQLhelper
{
///
///属性,连接字符串,只能被读取,不能给赋值
///
publicstringMyConnect
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ado 一部分 程序
![提示](https://static.bingdoc.com/images/bang_tan.gif)