大家好,小陆来为大家解答以上的问题。oledbparametercollection这个很多人还不知道,现在让我们一起来看看吧！
1、以下是我编写的一个操作access数据库的类，其他数据库可以参考修改，原理差不多的。
2、希望对你有帮助。
3、using System;using System.Collections.Generic;using System.Text;using System.Data.OleDb;using System.Configuration;using System.Data;namespace AutoEmailSender{ /// /// 数据库交互类 /// public class DB { /// /// 获得数据库连接 /// /// public static OleDbConnection GetDBConnection() { return new OleDbConnection(ConfigurationManager.AppSettings["ConnectString"]); } /// /// 查询结果集 /// /// 执行语句 /// 返回一个DataTable对象 public static DataTable ExecuteDataTable(string sql) { using (OleDbConnection con = GetDBConnection()) { OleDbCommand cmd = new OleDbCommand(sql, con); return ExecuteDataTable(cmd); } } /// /// 查询结果集 /// /// 执行语句的OleDbCommand命令 /// 返回一个DataTable对象 public static DataTable ExecuteDataTable(OleDbCommand cmd) { DataSet ds = new DataSet(); using (OleDbDataAdapter da = new OleDbDataAdapter(cmd)) { try { da.Fill(ds); } catch (Exception e) { throw e; } } if (ds.Tables.Count > 0) { ds.Tables[0].DefaultView.RowStateFilter = DataViewRowState.Unchanged | DataViewRowState.Added | DataViewRowState.ModifiedCurrent | DataViewRowState.Deleted; return ds.Tables[0]; } else return null; } /// /// 执行查询，并返回查询所返回的结果集中第一行的第一列。
4、忽略其他列或行。
5、 /// /// 查询语句 /// 返回结果集中第一行的第一列的object值 public static object ExecuteScalar(string sql) { using (OleDbConnection con = GetDBConnection()) { OleDbCommand cmd = new OleDbCommand(sql, con); return ExecuteScalar(cmd); } } /// /// 执行查询，并返回查询所返回的结果集中第一行的第一列。
6、忽略其他列或行。
7、 /// /// 查询命令 /// 返回结果集中第一行的第一列的object值 public static object ExecuteScalar(OleDbCommand cmd) { try { cmd.Connection.Open(); object obj = cmd.ExecuteScalar(); cmd.Connection.Close(); return obj; } catch (Exception error) { cmd.Connection.Close(); throw error; } } /// /// 更新数据集 /// /// 要更新的数据集 /// 插入SQL语句 /// 更新SQL语句 /// 删除SQL语句 /// public static int UpdateDataSet(DataTable dt, OleDbCommand insertCmd, OleDbCommand updateCmd, OleDbCommand deleteCmd) { using (OleDbDataAdapter da = new OleDbDataAdapter()) { da.InsertCommand = insertCmd; da.UpdateCommand = updateCmd; da.DeleteCommand = deleteCmd; //da.UpdateBatchSize = 0; //UpdateBatchSize:指定可在一次批处理中执行的命令的数量,在Access不被支持。
8、0:批大小没有限制。
9、1:禁用批量更新。
10、>1:更改是使用 UpdateBatchSize 操作的批处理一次性发送的。
11、 da.InsertCommand.UpdatedRowSource = UpdateRowSource.None; da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None; da.DeleteCommand.UpdatedRowSource = UpdateRowSource.None; try { int row = da.Update(dt); return row; } catch (Exception e) { throw e; } } } /// /// 返回一个查询语句执行结果的表结构 /// /// 查询语句,不支持复杂SQL /// public static DataTable GetTableSchema(string sql) { sql = sql.ToUpper(); DataTable dt = null; using (OleDbConnection con = GetDBConnection()) { OleDbCommand cmd = new OleDbCommand(sql, con); con.Open(); using (OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly | CommandBehavior.CloseConnection)) { dt = dr.GetSchemaTable(); } } return dt; } /// /// 根据输入的查询语句自动生成插入,更新,删除命令 /// /// 查询语句 /// 插入命令 /// 更新命令 /// 删除命令 public static void GenerateUpdateSQL(string sql, OleDbCommand insertCmd, OleDbCommand updateCmd, OleDbCommand deleteCmd) { sql = sql.ToUpper(); DataTable dt = GetTableSchema(sql); string tableName = dt.Rows[0]["BaseTableName"].ToString(); List updatePrimarykeys = new List();//主键参数集合 List deletePrimarykeys = new List();//主键参数集合,因为不能同时被OleDbCommand个命令引用,所以多申明一个 List insertFields = new List();//字段参数集合 List updateFields = new List();//字段参数集合 string columns = string.Empty, values = "", set = "", where = ""; foreach (DataRow dr in dt.Rows) { if (dr["IsAutoIncrement"].ToString().Equals("False")) { insertFields.Add(new OleDbParameter("@" + dr["BaseColumnName"].ToString(), (OleDbType)dr["ProviderType"], Convert.ToInt32(dr["ColumnSize"]), dr["BaseColumnName"].ToString())); updateFields.Add(new OleDbParameter("@" + dr["BaseColumnName"].ToString(), (OleDbType)dr["ProviderType"], Convert.ToInt32(dr["ColumnSize"]), dr["BaseColumnName"].ToString())); if (!string.IsNullOrEmpty(columns)) columns += ","; columns += dr["BaseColumnName"].ToString(); if (!string.IsNullOrEmpty(values)) values += ","; values += "@" + dr["BaseColumnName"].ToString(); if (!string.IsNullOrEmpty(set)) set += ","; set += dr["BaseColumnName"].ToString() + "=@" + dr["BaseColumnName"].ToString(); } if (dr["IsKey"].ToString().Equals("True")) { updatePrimarykeys.Add(new OleDbParameter("@OLD_" + dr["BaseColumnName"].ToString(), (OleDbType)dr["ProviderType"], Convert.ToInt32(dr["ColumnSize"]), ParameterDirection.Input, Convert.ToBoolean(dr["AllowDBNull"]), Convert.ToByte(dr["NumericScale"]), Convert.ToByte(dr["NumericPrecision"]), dr["BaseColumnName"].ToString(), DataRowVersion.Original, null)); deletePrimarykeys.Add(new OleDbParameter("@OLD_" + dr["BaseColumnName"].ToString(), (OleDbType)dr["ProviderType"], Convert.ToInt32(dr["ColumnSize"]), ParameterDirection.Input, Convert.ToBoolean(dr["AllowDBNull"]), Convert.ToByte(dr["NumericScale"]), Convert.ToByte(dr["NumericPrecision"]), dr["BaseColumnName"].ToString(), DataRowVersion.Original, null)); if (!string.IsNullOrEmpty(where)) where += " and "; where += dr["BaseColumnName"].ToString() + "=@OLD_" + dr["BaseColumnName"].ToString(); } } insertCmd.CommandText = string.Format("insert into {0} ({1}) values ({2})", tableName, columns, values); updateCmd.CommandText = string.Format("update {0} set {1} where {2}", tableName, set, where); deleteCmd.CommandText = string.Format("delete from {0} where {1}", tableName, where); insertCmd.Connection = GetDBConnection(); updateCmd.Connection = GetDBConnection(); deleteCmd.Connection = GetDBConnection(); foreach (OleDbParameter pa in insertFields) { insertCmd.Parameters.Add(pa); } foreach (OleDbParameter pa in updateFields) { updateCmd.Parameters.Add(pa); } foreach (OleDbParameter pa in updatePrimarykeys) { updateCmd.Parameters.Add(pa); } foreach (OleDbParameter pa in deletePrimarykeys) { deleteCmd.Parameters.Add(pa); } } }}1，连接字符串：表示数据库是什么，密码是什么，用户名是什么2，查询，修改，添加，删除命令3，数据库函数调用4，得出结果给你一个例子如下 public bool Insert(Device.Transmitter transmitter) { string sql = "Insert Into Device (SerialNumber,Address,CommunicationModule,PhoneNumber,InitInfo) Values (@SerialNumber,@Address,@CommunicationModule,@PhoneNumber,@InitInfo)"; OleDbParameter[] param = new OleDbParameter[5]; param[0] = new OleDbParameter("SerialNumber", OleDbType.VarChar, 10); param[0].Value = transmitter.SerialNumber; param[1] = new OleDbParameter("Address", OleDbType.VarChar, 50); param[1].Value = transmitter.Address; param[2] = new OleDbParameter("CommunicationModule", OleDbType.Integer); param[2].Value = transmitter.CommunicationModule; param[3] = new OleDbParameter("PhoneNumber", OleDbType.VarChar, 20); param[3].Value = transmitter.PhoneNumber; param[4] = new OleDbParameter("InitInfo", OleDbType.VarChar, 100); param[4].Value = transmitter.InitInfo; int count=AccessDB.ExecuteNonQuery(AccessDB.ConnectionString, System.Data.CommandType.Text, sql, param); return Convert.ToBoolean(count); } /// /// 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.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24)); /// /// a valid connection string for a OleDbConnection /// 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 number of rows affected by the command public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters) { OleDbCommand cmd = new OleDbCommand();using (OleDbConnection conn = new OleDbConnection(connectionString)) { try { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.Dispose(); return val; } catch { conn.Close(); throw; } } }要源码是吗？这个给你。
12、虽然是在网上Ctrl+C/Ctrl+V来的，但辛苦搜索了半天，希望对楼主有所帮助吧==========================================================using System;using System.Data;using System.Data.SqlClient;using System.Data.OleDb;namespace GuestBook{/// /// DbControl 的摘要描述。
13、/// public class DbControl:Classes.DataBaseType.DbOpen{//类成员定义。
14、private int record_total = 0;protected string DBTYPE,SQL_SELECT;protected SqlConnection sqlConn;protected OleDbConnection oleConn;protected SqlCommand sqlCmd;protected OleDbCommand oleCmd;protected DataSet ds = new DataSet();public int RecordTotal{get{switch (DBTYPE){case "SQL":sqlCmd.Cancel();sqlCmd.CommandText = SQL_SELECT;SqlDataReader sqlDr;sqlDr = sqlCmd.ExecuteReader();while (sqlDr.Read()){record_total++;}sqlDr.Close();break;case "OLE":oleCmd.Cancel();oleCmd.CommandText = SQL_SELECT;OleDbDataReader oleDr;oleDr = oleCmd.ExecuteReader();while (oleDr.Read()){record_total++;}oleDr.Close();break;}return record_total;}}public DbControl(string dbType,string dbName){//重载构造函数。
15、DBTYPE = dbType.ToUpper();switch (dbType.ToUpper()){case "SQL":sqlConn = this.SqlConnect(dbName);oleConn.Close();oleConn.Dispose();break;case "OLE":oleConn = this.OleConnect(dbName);oleConn.Close();oleConn.Dispose();break;}}public DbControl():base(){//// TODO: 在这里加入建构函式的程式码//}public void Open(string dbType,string dbName){//数据库文件打开。
16、DBTYPE = dbType.ToUpper();switch (dbType.ToUpper()){case "SQL":sqlConn = this.SqlConnect(dbName);break;case "OLE":oleConn = this.OleConnect(dbName);break;}}public SqlDataReader SqlGetReader(string strQuery){//返回一个SqlDataReader。
17、用於Sql serverSQL_SELECT = strQuery;sqlCmd = new SqlCommand(strQuery,sqlConn);SqlDataReader dr;try{sqlCmd.Connection.Open();}catch (Exception e){throw e;}dr = sqlCmd.ExecuteReader();return dr;}public OleDbDataReader OleGetReader(string strQuery){//返回一个OleDbDataReader。
18、用於OleDbSQL_SELECT = strQuery;oleCmd = new OleDbCommand(strQuery,oleConn);OleDbDataReader dr;try{oleCmd.Connection.Open();}catch (Exception e){throw e;}dr = oleCmd.ExecuteReader();return dr;}public int SqlRunCommand(string strQuery){//执行一条SQL语句。
19、包括记录插入、更新、删除。
20、用於Sql serverSQL_SELECT = strQuery;sqlCmd = new SqlCommand(strQuery,sqlConn);try{sqlCmd.Connection.Open();}catch (Exception e){throw e;}return sqlCmd.ExecuteNonQuery();}public。
本文到此分享完毕，希望对大家有所帮助。
免责声明：本文由用户上传，与本网站立场无关。财经信息仅供读者参考，并不构成投资建议。投资者据此操作，风险自担。 如有侵权请联系删除！
