数据连接

数据连接 1、 2、 创建表(acces,sql) 3、 查询 4、 插入记录 5、 删除记录 6、 在datagrid中显示 7、 调用存储过程 下面代码是连接sql server 2000的。 下面是在access中查询记录 private void Button1_Click(object sender, System.EventArgs e) {OleDbConnection myConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\data1.mdb") ; myConnection.Open(); string mySelectText = "select ID, 标题, 署名 from Guest order by id desc"; OleDbCommand objCmd=new OleDbCommand(mySelectText, myConnection); OleDbDataReader objRd =objCmd.ExecuteReader(); while(objRd.Read()) {Response.Write(objRd.GetString(1)+"
"); } myConnection.Close(); } 下面是在SQL,,,,中查询记录 private void Button2_Click(object sender, System.EventArgs e) {string SQLConnectionString="Password=lafposthe;Persist Security Info=True;User ID=sa;Initial Catalog=master;Data Source=127.0.0.1"; SqlConnection objConnection = new SqlConnection(SQLConnectionString); string strSql = "select sno,sname from student"; objConnection.Open(); SqlCommand cmd = new SqlCommand(strSql, objConnection); SqlDataReader objRd =cmd.ExecuteReader(); while(objRd.Read()) { Response.Write(objRd.GetString(1)+"
"); } objConnection.Close(); } 下面是调用datagrid查询数据 private void Button1_Click(object sender, System.EventArgs e) { string strConnection = ConfigurationSettings.AppSettings["SQLConnectionString"]; SqlConnection objConnection = new SqlConnection(strConnection); string strSql ="select sname from student"; DataSet objDataSet = new DataSet(); SqlDataAdapter objDataAdapter = new SqlDataAdapter(strSql, objConnection); objDataAdapter.Fill(objDataSet, "student"); DataView objDataView = new DataView(objDataSet.Tables["student"]); DataGrid1.DataSource = objDataView; DataGrid1.DataBind(); } 3.4.4调用存储过程 private void statistic() {string strConnection = ConfigurationSettings.AppSettings["SQLConnectionString"]; SqlConnection objConnection = new SqlConnection(strConnection); objCmd = new SqlCommand("branch_stat", objConnection); objCmd.CommandType = CommandType.StoredProcedure; objCmd.ExecuteNonQuery(); objConnection.Close();} 下面是创建存储过程 drop trigger dletestudent go create trigger dletestudent on student for delete as declare @counter int select @counter=count(*) from score where sno in(SELECT sno FROM deleted ) if(@counter>0) begin rollback end go delete from student where sno=108 select * from student drop proc changeteacher go CREATE proc changeteacher (@tno1 int) AS begin declare @counter int declare @returnvalue int set @returnvalue=0 select @counter=count(*) from teacher where tno=@tno1 if(@counter>0) BEGIN set @returnvalue=1 update teacher set proff='tno1' where tno=@tno1 end return @returnvalue end go EXECUTE changeteacher 804 插入记录, private void btnOK_Click(object sender, System.EventArgs e) { string strConnection = ConfigurationSettings.AppSettings["SQLConnectionString"]; SqlConnection objConnection = new SqlConnection(strConnection); try { objConnection.Open(); string temp = UserName.Text; string temp1 = UserName.Text.ToString(); string user_name=UserName.Text.Trim(); string password=Password.Text.Trim(); string cfmpwd = CfmPwd.Text.Trim(); string chinese_name=RealName.Text.Trim(); string table_name="user_fl"; string user_type=UserType.SelectedItem.Value; string user_desc = UserDesc.Text; if (cfmpwd.Equals(password)) string strSql = "insert into user_fl(user_name, password, chinese_name, table_name, user_type, user_desc, " + "create_date, out_date, if_use, id_number, user_bank) values('"+user_name+"', '"+password+"', '" + chinese_name +"', '"+table_name+"','" +user_type+"','"+user_desc+"',getdate(), '2005-01-01','yes',0,'')"; SqlCommand cmd=new SqlCommand(strSql,objConnection); cmd.ExecuteNonQuery(); prompt.Text = "新增用户成功，您可以在此页面继续添加新的用户～"; } else { prompt.Text = "您两次输入的密码不相同，请重新输入～"; } } catch (Exception objE) { prompt.Text = objE.Message;//"新增用户失败～"; } finally { objConnection.Close(); } } create table student(sno int,sname char(12),sex char(4),birthday datetime,class char(10) ) go insert into student values(108,'曾化','男','77/09/01','95033'); insert into student values(105,'明化','男','77/09/01','95033'); insert into student values(107,'王丽','女','77/09/01','95033'); insert into student values(101,'李军','男','77/09/01','95033'); insert into student values(109,'王芳','女','77/09/01','95033'); insert into student values(103,'叶军','男','77/09/01','95033'); go 下面代码是连接oracle的。 using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.OracleClient; public partial class oracle : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } //简单查询 protected void Button1_Click(object sender, EventArgs e) { string ConnectionString = "Server=localhost;Data Source=test;user=system;password=manager;"; //写连接串 OracleConnection conn = new OracleConnection(ConnectionString);//创建一个新连接 try { conn.Open(); OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "select * from student";//在这儿写sql语句 OracleDataReader odr = cmd.ExecuteReader();//创建一个OracleDateReader对象 while (odr.Read())//读取数据，如果odr.Read()返回为false的话，就说明到记录集的尾部了 { Response.Write(odr.GetInt32(0)); Response.Write(" "); Response.Write(odr.GetOracleString(1).ToString());//输出字段1，这个数是字段索引， Response.Write(" "); Response.Write(odr.GetOracleString(2).ToString()); Response.Write(" "); Response.Write(odr.GetDateTime(3).ToString()); Response.Write(" "); Response.Write(odr.GetOracleString(4).ToString()); Response.Write("
"); } odr.Close(); } catch (Exception ee) { Response.Write(ee.Message); //如果有错误，输出错误信息 } finally { conn.Close(); //关闭连接 } } //查询 protected void Button2_Click(object sender, EventArgs e) { string ConnectionString = "Server=localhost;Data Source=test;user=system;password=manager;"; //写连接串 OracleConnection conn = new OracleConnection(ConnectionString);//创建一个新连接 string strSql = "select * from student"; DataSet objDataSet = new DataSet(); OracleDataAdapter objDataAdapter = new OracleDataAdapter(strSql, conn); objDataAdapter.Fill(objDataSet, "student"); DataView objDataView = new DataView(objDataSet.Tables["student"]); GridView1.DataSource = objDataView; GridView1.DataBind(); } protected void Button3_Click(object sender, EventArgs e) { string ConnectionString = "Server=localhost;Data Source=test;user=system;password=manager;"; //写连接串 OracleConnection conn = new OracleConnection(ConnectionString);//创建一个新连接 try { conn.Open(); string temp = TextBox1.Text; string temp1 = TextBox2.Text; string strSql = "insert into student(sno,sname) values('"+temp+"', '"+temp1+"')"; OracleCommand cmd=new OracleCommand(strSql,conn); cmd.ExecuteNonQuery(); prompt.Text = "新增用户成功，您可以在此页面继续添加新的用户～"; } catch (Exception objE) { prompt.Text = objE.Message;//"新增用户失败～"; } finally { conn.Close(); } } } a' or 'a'='a a' or 'a'='a //Oracle中调用存储过程 protected void Button2_Click(object sender, EventArgs e) { try { string strConnection = "Server=localhost;Data Source=test;user=scott;password=tiger;"; OracleConnection objConnection = new OracleConnection(strConnection); objConnection.Open(); OracleCommand objCmd = new OracleCommand("query_stu", objConnection); //指名是存储过程 objCmd.CommandType = CommandType.StoredProcedure; //添加参数,和参数类型 objCmd.Parameters.Add("sno_p", OracleType.Number); //传值 objCmd.Parameters["sno_p"].Value = 107; //执行存储过程,成功则返回1 int res = objCmd.ExecuteNonQuery(); objConnection.Close(); } catch (Exception objE) { Label1.Text = objE.Message; } } 在ASP.net程序中常常需要调用数据库中存储过程或函数等，调用Oracle数据库存 储过程的方法与调用sqlServer2000中的存储过程类似。只是.NET只支持oracle 数据库包中的存储过程,不支持函数，不能像调用存储过程那样直接调用Oracle的 函数。但如果有需要的话还是可以实现的。个人总结了一下，第一种方法是:在 Oracle数据库后台，新建一个存储过程，在此存储过程中直接调用你想要调用的函 数，函数的返回值传给存储过程中定义的一个变量就可以了。然后在你的.net程序 中调用你定义的存储过程就可以了。但这种方法需要修改后台数据库，有时前台开 发人员不具有修改数据库的权限，这种方法就显得被动一些了。第二种方法是通过 在定义OracleCommand时，指定相应的command Text为: "select 函数 名 from dual"，当然相应的CommandType 为CommandType.Text，而不是 CommandType.StoredProcedure，如果函数有参数，参数与一般的sql语句设置方法 是一致的。 以下是一段实例: OracleConnection conn = new OracleConnection(DBHelper.conn); OracleCommand comm= new OracleCommand("select apps.SGMIS_WHP.GetOrder(: vDJDM,:vDWDM) from dual", conn);//此函建在包中，需要在函数前加包名 OracleParameter p0 = new OracleParameter("vDJDM", OracleType.Var Char, 20); p0.Direction = ParameterDirection.Input; p0.Value = vDJDM; OracleParameter p1 = new OracleParameter("vDWDM", OracleType.Var 20); Char, p1.Direction = ParameterDirection.Input; p1.Value = vDWDM; comm.Parameters.Add(p0); comm.Parameters.Add(p1); comm.CommandType = CommandType.Text; string result; try { if (conn.State == ConnectionState.Closed) conn.Open(); result = Convert.ToString(comm.ExecuteScalar()); return result; } finally { if (conn.State == ConnectionState.Open) conn.Close(); }