C#如何使用数据库

     数据库，在我们开发项目中那是最常见的了，无论复杂的项目、还是简单的项目，只要涉及数据的操作（增删改查），都会用到数据库。当然使用的数据库的种类根据项目的需求不同，也是不同的。在我所做的项目中就是用过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统计用户在线时间为例）