转自:http://blog.csdn.net/yysyangyangyangshan/article/details/6999304
数据库,在我们开发项目中那是最常见的了,无论复杂的项目、还是简单的项目,只要涉及数据的操作(增删改查),都会用到数据库。当然使用的数据库的种类根据项目的需求不同,也是不同的。在我所做的项目中就是用过ACCESS 2000、SQL Server 2000、Oracle 10g,因此简单总结下c#如何使用数据库的。程序执行sql语句(包括单条sql,多条sql,存储过程,函数等等)大体有三个步骤:连接数据库,执行sql语句,返回结果。那么我们来看看c#是如何完成这三个步骤的。无论哪种数据库都是这三个步骤,那么就不按数据库分了,按照返回结果的类型来分类吧。
数据库连接用的字符串:
即下面代码的connstr
- "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="数据库路径";Persist Security Info=True;Jet OLEDB:Database Password="密码"
1、 执行sql语句返回受影响的行数
(1)、一条sql语句
- public static int ExecuteNonQuery(string connStr, string sql)
- {
- OleDbConnection conn = null;
- try
- {
- conn = new OleDbConnection(connStr);
- conn.Open();
- OleDbCommand command = new OleDbCommand(sql, conn);
- return command.ExecuteNonQuery();
- }
- catch
- {
- return 0;
- }
- finally
- {
- if (conn != null)
- conn.Close();
- }
- }
(2)、多条sql语句,返回受影响行数。由于多条sql为了保证数据库的正确性,最好使用事务方式运行。
- public static int ExecuteNonQuery(string connStr, List<string> sqlList)
- {
- int executeCount = 0;
- OleDbTransaction transaction = null;
- OleDbConnection conn = null;
- try
- {
- conn = new OleDbConnection(connStr);
- conn.Open();
- transaction = conn.BeginTransaction();
- OleDbCommand command = new OleDbCommand();
- command.Connection = conn;
- command.Transaction = transaction;
- for (int i = 0; i < sqlList.Count; i++)
- {
- command.CommandText = sqlList[i];
- executeCount += command.ExecuteNonQuery();
- }
- transaction.Commit();
- }
- catch
- {
- if (transaction != null)
- transaction.Rollback();
- return 0;
- }
- finally
- {
- if (conn != null)
- conn.Close();
- }
- return executeCount;
- }
2、返回DataSet
- public static DataSet ExecuteQueryReturnDataSet(string connStr, string sql)
- {
- OleDbConnection conn = null;
- DataSet ds = null;
- try
- {
- conn = new OleDbConnection(connStr);
- ds = new DataSet();
- conn.Open();
- OleDbDataAdapter command = new OleDbDataAdapter(sql, conn);
- command.Fill(ds, "datasetName");
- }
- catch
- {
- }
- finally
- {
- if (conn != null)
- conn.Close();
- }
- return ds;
- }
3、返回OleDbDataReader
- public static OleDbDataReader ExecuteQueryReturnReader(string sql, string connStr)
- {
- OleDbConnection conn = new OleDbConnection();
- OleDbDataReader reader = null;
- try
- {
- conn = new OleDbConnection(connStr);
- conn.Open();
- OleDbCommand command = conn.CreateCommand();
- command.CommandText = sql;
- reader = command.ExecuteReader();
- }
- catch
- {
- }
- finally
- {
- if (conn != null)
- conn.Close();
- }
- return reader;
- }
4、执行存储过程并返回受影响行数(以Oracle为例)
- public static int RunProcedure(string storedProcName, IDataParameter[] parameters)
- {
- int result = 0;
- try
- {
- using (OracleConnection connection = new OracleConnection(GetConnectionString()))
- {
- connection.Open();
- OracleCommand command = BuildQueryCommand(connection, storedProcName, parameters);
- result = command.ExecuteNonQuery();
- }
- }
- catch (Exception e)
- {
- }
- return result;
- }
- private static OracleCommand BuildQueryCommand(OracleConnection connection, string storedProcName,
- IDataParameter[] parameters)
- {
- OracleCommand command = new OracleCommand(storedProcName, connection);
- command.CommandType = CommandType.StoredProcedure;
- foreach (OracleParameter parameter in parameters)
- {
- command.Parameters.Add(parameter);
- }
- return command;
- }
5、带参数的sql语句
- public static int ExecuteNonQuery(string sql,string connStr, OleDbParameter[] commandParameters)
- {
- int count = 0;
- OleDbCommand cmd = new OleDbCommand();
- OleDbConnection conn = null;
- try
- {
- conn = new OleDbConnection(connStr);
- PrepareCommand(cmd, conn,sql, commandParameters);
- count = cmd.ExecuteNonQuery();
- cmd.Parameters.Clear();
- }
- catch
- {
- }
- finally
- {
- if (conn != null)
- {
- conn.Close();
- }
- }
- return count;
- }
- private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, string sql, OleDbParameter[] cmdParms)
- {
- if (conn.State != ConnectionState.Open)
- conn.Open();
- cmd.Connection = conn;
- cmd.CommandText = sql;
- cmd.CommandType = CommandType.Text;
- if (cmdParms != null)
- {
- foreach (OleDbParameter parm in cmdParms)
- cmd.Parameters.Add(parm);
- }
- }
一般常用的就这么几种,数据库的几个重要对象分别是:OleDbCommand、OleDbConnection、OleDbDataReader、OleDbDataAdapter、DataSet。对于不同数据库,有几个对象需要对应相应数据库的对象。OleDbCommand、OleDbConnection、OleDbDataReader、OleDbDataAdapter,Oracle下OleDb换成Oracle,Sql Server下 则换成SQL。
你还可能感兴趣:c#调用oracle存储过程 Oracle存储过程的格式 c#如何使用webservice、存储过程及存储过程的创建(简单模仿类似QQ统计用户在线时间为例)