《2022年用于对数据库进行操作的类库---经验的总结C#知识 .pdf》由会员分享，可在线阅读，更多相关《2022年用于对数据库进行操作的类库---经验的总结C#知识 .pdf（23页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、用于对数据库进行操作的类库- 经验的总结( 二)C#publicstaticstring GetData(string firstName,string lastName,ref DataSet ds) SqlConnection conn=new SqlConnection(connectionString) ; SqlDataAdapter da=new SqlDataAdapter(Pro_GetUserInfo,conn); para0=new SqlParameter(firstName,firstName); para1=new SqlParameter(lastName,last
2、Name); da.SelectCommand.Parameters.Add(para0); da.SelectCommand.Parameters.Add(para1); da.SelectCommand.CommandType=CommandType.StoredProcedure; try conn.Open(); da.Fill(ds); conn.Close(); catch (Exception ex) If(conn.State = ConnectionState.Open) conn.Close(); return ex.ToString(); using System; us
3、ing System.Data; using System.Configuration; using System.Data.SqlClient; using System.Data.SqlTypes; using System.Collections; publicclass CSqlHelper string ConnStr = String.Empty; public CSqlHelper() /ConnStr = ConfigurationSettings.AppSettingssqlConstr; ConnStr = sqlConstr; publicstaticstring sql
4、Constr = ; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 23 页 - - - - - - - - - public CSqlHelper(string Str) try this .ConnStr = Str; catch (Exception ex) throw ex; /返回 connection对象 , 并打开连接/public SqlConnection ReturnConn() SqlConnection Conn = new SqlConnect
5、ion(this .ConnStr); Conn.Open(); return Conn; publicvoid Dispose(SqlConnection Conn) if (Conn != null ) if (Conn.State = ConnectionState.Open) Conn.Close(); Conn.Dispose(); GC.Collect(); /运行 SQL 语句 (连接在函数中打开并且关闭) /是 sql 语句 ,或者不带参数的存储过程. /publicvoid RunProc(string SQL) 名师资料总结 - - -精品资料欢迎下载 - - - - -
6、- - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 23 页 - - - - - - - - - SqlConnection Conn; Conn = new SqlConnection(ConnStr); Conn.Open(); SqlCommand Cmd; Cmd = CreateCmd(SQL, Conn); try Cmd.ExecuteNonQuery(); catch thrownew Exception(SQL); Dispose(Conn); return; /运行 SQL 语句返回DataReader /连接打开
7、,但没有关闭 . /SqlDataReader对象 .public SqlDataReader RunProcGetReader(string SQL, out SqlConnection OutConn) SqlConnection Conn; Conn = new SqlConnection(ConnStr); Conn.Open(); SqlCommand Cmd; Cmd = CreateCmd(SQL, Conn); SqlDataReader Dr; try Dr = Cmd.ExecuteReader(CommandBehavior.Default); catch throwne
8、w Exception(SQL); /Dispose(Conn); OutConn = Conn; return Dr; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 23 页 - - - - - - - - - /生成 Command对象/没有打开连接/private SqlCommand CreateCmd(string SQL, SqlConnection Conn) SqlCommand Cmd; Cmd = new SqlCommand(SQL, Conn);
9、 return Cmd; /生成 Command对象/打开连接 ,但是没有关闭.将连接以返回参数形式输出/private SqlCommand CreateCmd(string SQL, out SqlConnection OutConn) SqlConnection Conn; Conn = new SqlConnection(ConnStr); Conn.Open(); SqlCommand Cmd; Cmd = new SqlCommand(SQL, Conn); OutConn = Conn; return Cmd; /返回 adapter对象/连接打开/连接没有关闭. /public
10、 SqlDataAdapter CreateDa(string SQL, out SqlConnection OutConn) SqlConnection Conn; Conn = new SqlConnection(ConnStr); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 23 页 - - - - - - - - - Conn.Open(); SqlDataAdapter Da; Da = new SqlDataAdapter(SQL, Conn); OutC
11、onn = Conn; return Da; /运行 SQL 语句 ,返回 DataSet对象/已经关闭了连接/SQL 语句 /DataSet对象 public DataSet RunProc(string SQL, DataSet Ds) SqlConnection Conn; Conn = new SqlConnection(ConnStr); Conn.Open(); SqlDataAdapter Da; /Da = CreateDa(SQL, Conn); Da = new SqlDataAdapter(SQL, Conn); try Da.Fill(Ds); catch (Excep
12、tion Err) throw Err; Dispose(Conn); return Ds; /运行 SQL 语句 ,返回 DataSet对象/已经关闭了连接/SQL 语句 /DataSet对象 /表名 public DataSet RunProc(string SQL, DataSet Ds, string tablename) SqlConnection Conn = new SqlConnection(); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 23 页
13、- - - - - - - - - SqlDataAdapter Da; Da = CreateDa(SQL, out Conn); try Da.Fill(Ds, tablename); catch (Exception Ex) throw Ex; Dispose(Conn); return Ds; publicint RunSQL(string SQL, SqlParameter prams) SqlConnection Conn; SqlCommand Cmd = CreateSQLCmd(SQL, prams, out Conn); return (Cmd.ExecuteNonQuer
14、y(); /运行 SQL 语句 ,返回 DataSet对象/已经关闭了连接/SQL 语句 /DataSet对象 /表名 public DataSet RunProc(string SQL, DataSet Ds, int StartIndex, int PageSize, string tablename) SqlConnection Conn = new SqlConnection(); SqlDataAdapter Da; Da = CreateDa(SQL, out Conn); try Da.Fill(Ds, StartIndex, PageSize, tablename); 名师资料
15、总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 23 页 - - - - - - - - - catch (Exception Ex) throw Ex; Dispose(Conn); return Ds; /检验是否存在数据/已经关闭了连接/publicbool ExistDate(string SQL) SqlConnection Conn; Conn = new SqlConnection(ConnStr); Conn.Open(); SqlDataReader Dr; D
16、r = CreateCmd(SQL, Conn).ExecuteReader(); if (Dr.Read() Dispose(Conn); returntrue ; else Dispose(Conn); returnfalse ; /返回 SQL 语句执行结果的第一行第一列/已经关闭了连接/字符串 publicobject ReturnValue(string SQL) SqlConnection Conn; Conn = new SqlConnection(ConnStr); Conn.Open(); object result; SqlDataReader Dr; 名师资料总结 - -
17、 -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 23 页 - - - - - - - - - try Dr = CreateCmd(SQL, Conn).ExecuteReader(); if (Dr.Read() result = Dr0; Dr.Close(); else result = ; Dr.Close(); catch thrownew Exception(SQL); Dispose(Conn); return result; /返回 SQL 语句第一行 ,第 ColumnI列
18、, /已经关闭了连接/字符串 publicstring ReturnValue(string SQL, int ColumnI) SqlConnection Conn; Conn = new SqlConnection(ConnStr); Conn.Open(); string result; SqlDataReader Dr; try Dr = CreateCmd(SQL, Conn).ExecuteReader(); catch thrownew Exception(SQL); if (Dr.Read() 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - -
19、 - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 23 页 - - - - - - - - - result = DrColumnI.ToString(); else result = ; Dr.Close(); Dispose(Conn); return result; /-运行存储过程- /= /- /生成一个存储过程使用的sqlcommand. /并返回已经打开的连接/存储过程名 . /存储过程入参数组. /sqlcommand对象 .private SqlCommand CreateCmd(string procName, SqlParameter
20、 prams, out SqlConnection OutConn) SqlConnection Conn; Conn = new SqlConnection(ConnStr); Conn.Open(); SqlCommand Cmd = new SqlCommand(procName, Conn); Cmd.CommandType = CommandType.StoredProcedure; if (prams != null ) foreach (SqlParameter parameter in prams) if (parameter != null ) Cmd.Parameters.
21、Add(parameter); OutConn = Conn; return Cmd; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 23 页 - - - - - - - - - private SqlCommand CreateSQLCmd(string SQL, SqlParameter prams, out SqlConnection OutConn) SqlConnection Conn; Conn = new SqlConnection(ConnStr); C
22、onn.Open(); SqlCommand Cmd = new SqlCommand(SQL, Conn); Cmd.CommandType = CommandType.Text; if (prams != null ) foreach (SqlParameter parameter in prams) if (parameter != null ) Cmd.Parameters.Add(parameter); OutConn = Conn; return Cmd; /- /为存储过程生成一个SqlCommand对象/存储过程名 /存储过程参数/SqlCommand对象 private Sq
23、lCommand CreateCmd(string procName, SqlParameter prams, SqlDataReader Dr) SqlConnection Conn; Conn = new SqlConnection(ConnStr); Conn.Open(); SqlCommand Cmd = new SqlCommand(procName, Conn); Cmd.CommandType = CommandType.StoredProcedure; if (prams != null ) foreach (SqlParameter parameter in prams)
24、Cmd.Parameters.Add(parameter); Cmd.Parameters.Add( 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 23 页 - - - - - - - - - new SqlParameter(ReturnValue, System.Data.SqlDbType.Int, 4, ParameterDirection.ReturnValue, false , 0, 0, string.Empty, DataRowVersion.Defa
25、ult, null ); return Cmd; private SqlCommand CreateCmd(string procName, SqlDataReader Dr) SqlConnection Conn; Conn = new SqlConnection(ConnStr); Conn.Open(); SqlCommand Cmd = new SqlCommand(procName, Conn); Cmd.CommandType = CommandType.StoredProcedure; return Cmd; private SqlCommand CreateSqlCmd(str
26、ing procName, SqlDataReader Dr) SqlConnection Conn; Conn = new SqlConnection(ConnStr); Conn.Open(); SqlCommand Cmd = new SqlCommand(procName, Conn); return Cmd; /运行存储过程, 返回 . /存储过程名 /存储过程参数/SqlDataReader对象 public SqlDataReader RunProc(string procName, SqlParameter prams, SqlDataReader Dr) SqlCommand
27、 Cmd = CreateCmd(procName, prams, Dr); Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); return Dr; / CreateSqlCmd /执行/名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 23 页 - - - - - - - - - /public SqlDataReader RunProc(string procName, SqlDa
28、taReader Dr) SqlCommand Cmd = CreateCmd(procName, Dr); Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); return Dr; public SqlDataReader RunSQL(string SQL, SqlDataReader Dr) SqlCommand Cmd = CreateSqlCmd(SQL, Dr); Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
29、; return Dr; /- /运行存储过程/查询/只返回结果集的第一行第一列/存储过程名 /存储过程参数publicstring RunProc(string procName, SqlParameter prams) SqlDataReader Dr; SqlConnection Conn = new SqlConnection(); SqlCommand Cmd = CreateCmd(procName, prams, out Conn); Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); if (
30、Dr.Read() return Dr.GetValue(0).ToString(); else return ; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 23 页 - - - - - - - - - publicvoid RunProcNoReturn(string procName, SqlParameter prams) SqlConnection Conn = new SqlConnection(); SqlCommand Cmd = CreateCmd
31、(procName, prams, out Conn); Cmd.ExecuteNonQuery(); Conn.Close(); /运行存储过程/执行命令 ; /返回 SqlCommand /用于存储过程有返回值的情况/存储过程名 /参数 /SqlCommandpublicobject ReturnValueByProc(string procName, SqlParameter prams) object result = null ; SqlConnection Conn; SqlCommand Cmd = CreateCmd(procName, prams, out Conn); re
32、sult = Cmd.ExecuteScalar(); Dispose(Conn); return result; /运行存储过程/存储过程名 /参数 /初始化后SqlCommand/命令执行影响的行数/SqlCommandpublic SqlCommand RunProc(string procName, SqlParameter prams, SqlCommand OutCmd, outint rowAffect) rowAffect = -1; SqlConnection Conn; SqlCommand Cmd = CreateCmd(procName, prams, out Conn
33、); rowAffect = Cmd.ExecuteNonQuery(); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 23 页 - - - - - - - - - OutCmd = Cmd; Dispose(Conn); return Cmd; /适用于没有返回值的存储过程/返回影响的行数publicint RunProc(string procName, SqlParameter prams, int rowAffect) SqlConnection Conn;
34、 SqlCommand Cmd = CreateCmd(procName, prams, out Conn); rowAffect = Cmd.ExecuteNonQuery(); this .Dispose(Conn); return rowAffect; /运行存储过程, 返回 dataset. /存储过程名 . /存储过程入参数组. /dataset对象 .public DataSet RunProc(string procName, SqlParameter prams, DataSet Ds) SqlConnection Conn = new SqlConnection(); Sql
35、Command Cmd = CreateCmd(procName, prams, out Conn); SqlDataAdapter Da = new SqlDataAdapter(Cmd); try Da.Fill(Ds); catch (Exception Ex) string dd = Ex.Message; throw Ex; this .Dispose(Conn); return Ds; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 14 页，共 23 页 - - - -
36、 - - - - - public DataSet RunProc(string procName, SqlParameter prams, DataSet Ds, string tablename) SqlConnection Conn = new SqlConnection(); SqlCommand Cmd = CreateCmd(procName, prams, out Conn); SqlDataAdapter Da = new SqlDataAdapter(Cmd); try Da.Fill(Ds, tablename); catch (Exception Ex) throw Ex
37、; this .Dispose(Conn); return Ds; / -都可运行的/privatestatic Hashtable parmCache = Hashtable.Synchronized(new Hashtable(); / Execute a SqlCommand (that returns no resultset) against the database specified in the connection string / using the provided parameters. / e.g.: / int result = ExecuteNonQuery(co
38、nnString, CommandType.StoredProcedure, PublishOrders, new SqlParameter(prodid, 24); /a valid connection string for a SqlConnection/the CommandType (stored procedure, text, etc.)/the stored procedure name or T-SQL command/an array of SqlParamters used to execute the command/an int representing the nu
39、mber of rows affected by the command publicstatic DataSet ExecuteReturnDataSet(string connectionString, Comma名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 15 页，共 23 页 - - - - - - - - - ndType cmdType, string cmdText, params SqlParameter commandParameters) SqlCommand
40、 cmd = new SqlCommand(); SqlDataAdapter adapt; DataSet ds = new DataSet(); try using (SqlConnection conn = new SqlConnection(connectionString) PrepareCommand(cmd, conn, null , cmdType, cmdText, commandParameters); adapt = new SqlDataAdapter(cmd); /int val = cmd.ExecuteNonQuery(); adapt.Fill(ds); cmd
41、.Parameters.Clear(); return ds; catch (Exception ex) string msg = ex.Message; returnnull ; / Execute a SqlCommand (that returns no resultset) against the database specified in the connection string / using the provided parameters. / e.g.: / int result = ExecuteNonQuery(connString, CommandType.Stored
42、Procedure, PublishOrders, new SqlParameter(prodid, 24); /a valid connection string for a SqlConnection/the CommandType (stored procedure, text, etc.)/the stored procedure name or T-SQL co名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 16 页，共 23 页 - - - - - - - - - mma
43、nd/an array of SqlParamters used to execute the command/an int representing the number of rows affected by the command publicstaticint ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter commandParameters) SqlCommand cmd = new SqlCommand(); using (SqlCon
44、nection conn = new SqlConnection(connectionString) PrepareCommand(cmd, conn, null , cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; / Execute a SqlCommand (that returns no resultset) against an existing database connection / using the provid
45、ed parameters. / e.g.: / int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, PublishOrders, new SqlParameter(prodid, 24); /an existing database connection/the CommandType (stored procedure, text, etc.)/the stored procedure name or T-SQL command/an array of SqlParamters used to exec
46、ute the command/an int representing the number of rows affected by the command publicstaticint ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter commandParameters) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 17 页，共
47、23 页 - - - - - - - - - SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null , cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; / Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction / using the
48、 provided parameters. / e.g.: / int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, PublishOrders, new SqlParameter(prodid, 24); /an existing sql transaction/the CommandType (stored procedure, text, etc.)/the stored procedure name or T-SQL command/an array of SqlParamters used to e
49、xecute the command/an int representing the number of rows affected by the command publicstaticint ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter commandParameters) SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType,
50、 cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; / Execute a SqlCommand that returns a resultset against the database specified in the connection string / using the provided parameters. /名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理