C#数据库查询的基本步骤(Sql Server)

1. 连接字符串。一般定义在项目的web.config文件中。

   <configuration>
     <connectionStrings>
       <add name="OnlineShopConnectionString"
            connectionString="data source=.\SQLEXPRESS; Initial Catalog=OnlineShoppingDatabase; Integrated Security=SSPI"
            providerName="System.Data.SqlClient" />
     </connectionStrings>

   public string connectionString = ConfigurationManager.ConnectionStrings["OnlineShopConnectionString"].ConnectionString;//通过ConfigurationManager获取连接字符串
2. 构建SqlConnection对象。

   SqlConnection connection = new SqlConnection(connectionString);

3. 打开连接。

   connection.Open();

4. 构建SqlCommand对象。根据不同的CommandType(Text,StoredProcedure,TableDirect)构建不同的SqlCommand对象。
   Text 即为sql语句。
   string sql="select * from Student";  SqlCommand command=new SqlCommand(sql,connection);
   StoredProcedure为存储过程。
   string storedProcdureName="procGetAllStudent";
   SqlCommand command=new SqlCommand(storedProcdureName,connection);
   command.CommandType=CommandType.StoredProcedure;//同时需指定CommandType
   //同时要为存储过程传参。遍历IDataParameter[].

   foreach (SqlParameter parameter in parameters)                {                    command.Parameters.Add(parameter);                }

5. 根据需要的返回值类型，构建返回值。若期望得到SqlDataReader对象。SqlDataReader reader = command.ExecuteReader();若期望得到影响的行数。int row=command.ExecuteNonQuery();若期望得到DataSet/DataTable对象。DataTable dt = new DataTable();SqlDataAdapter adapter = new SqlDataAdapter();adapter.SelectCommand = command;adapter.Fill(dt);

6. 关闭连接。connection.Close();

以下是自己定义的数据库操作的相关类。

public sealed class ConnectionManager    {        private ConnectionManager() { }        [ThreadStatic]        public static SqlConnection sqlConnection;        const string ConnectionString = "OnlineShopConnectionString";        public static SqlConnection Connection        {            get            {                if (sqlConnection == null)                {                    sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString);                }                return sqlConnection;            }        }   }

 public sealed class SqlHelper    {        private SqlHelper()        {            //The class cannot be initialized.        }        

        /// <summary>        /// Gets a instance of sqlcommand        /// </summary>         /// <param name="sqlConnection">Represents an open sqlConnection to SQL Server database</param>        /// <param name="storedProcName">Stored procedure</param>        /// <param name="parameters">Parameter</param>        /// <returns>An object of sqlcommand</returns>        private static SqlCommand BuildQueryCommand(SqlConnection sqlConnection, CommandType commandType, string commandText, IDataParameter[] parameters)        {            SqlCommand command = new SqlCommand(commandText, sqlConnection);            command.CommandType = commandType;            if (parameters != null)            {                foreach (SqlParameter parameter in parameters)                {                    command.Parameters.Add(parameter);                }            }            return command;        }        public static SqlDataReader ExecuteReader(SqlConnection sqlConnection, CommandType commandType, string commandText, SqlParameter[] parameters)        {            if (null == sqlConnection)            {                throw new ArgumentNullException(Constant.ConnectionString);            }            SqlCommand command = BuildQueryCommand(sqlConnection, commandType, commandText, parameters);            return command.ExecuteReader();        }

        public static int ExecuteNonQuery(SqlConnection sqlConnection, CommandType commandType, string commandText, SqlParameter[] parameters)        {            if (null == sqlConnection)            {                throw new ArgumentNullException(Constant.ConnectionString);            }            SqlCommand command = BuildQueryCommand(sqlConnection, commandType, commandText, parameters);

            return command.ExecuteNonQuery();        }            }

C# 中数据库操作中的回归操作

private TransactionOptions options = new TransactionOptions();            options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;            options.Timeout = new TimeSpan(0, 0, 30); 

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))            {                try                {

                    if (product.RetailPrice > product.DiscountPrice)                    {                        ConnectionManager.Connection.Open();                        LogHelper.Log(string.Format(CultureInfo.CurrentCulture, Constant.AddProduct + product.ProductId));                        result = DalProduct.AddProduct(ConnectionManager.Connection, product);                    }                    else                    {                        return false;                    }                    scope.Complete();                }                catch (Exception ex)                {                    LogHelper.Log(ex.Message);                    throw;                }                finally                {                    if (ConnectionManager.Connection.State != ConnectionState.Closed)                    {                        ConnectionManager.Connection.Close();                    }                }            }