winform中通用的数据库操作类

using System; using System.Collections.Generic; using System.Text; using System.Collections; using System.Data; using System.Data.SqlClient;

namespace 酒店管理系统 {     public class DBManage     {         private static string connectionString ;

        //获取连接字符串，参数为服务器、数据库、用户名、密码         public static void connectToServer(string server,string database,string user,string pwd)         {

            string con = "server="+server+";database="+database+";uid="+user+";pwd="+pwd;             DBManage.connectionString=con;         }

        //执行SQL语句，返回影响的记录数         public static int ExecuteSql(string SQLString)         {             SqlConnection connection = new SqlConnection(connectionString);             SqlCommand cmd = new SqlCommand(SQLString, connection);                            try             {                 connection.Open();                 int rows = cmd.ExecuteNonQuery();                 return rows;             }             catch (System.Data.SqlClient.SqlException E)             {                 connection.Close();                 throw new Exception(E.Message);             }                     }

        //执行多条SQL语句，实现数据库事务。         public static void ExecuteSqlTran(ArrayList SQLStringList)         {             SqlConnection conn = new SqlConnection(connectionString);                        conn.Open();             SqlCommand cmd = new SqlCommand();             cmd.Connection = conn;             SqlTransaction tx = conn.BeginTransaction();             cmd.Transaction = tx;             try             {                 for (int n = 0; n < SQLStringList.Count; n++)                 {                     string strsql = SQLStringList[n].ToString();                     if (strsql.Trim().Length > 1)                     {                         cmd.CommandText = strsql;                         cmd.ExecuteNonQuery();                     }                 }                 tx.Commit();             }             catch (System.Data.SqlClient.SqlException E)             {                 tx.Rollback();                 throw new Exception(E.Message);             }                    }

        //执行一条计算查询结果语句，返回查询结果（object）。         public static object GetSingle(string SQLString)         {             SqlConnection connection = new SqlConnection(connectionString);             SqlCommand cmd = new SqlCommand(SQLString, connection);                     try             {                 connection.Open();                 object obj = cmd.ExecuteScalar();                 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))                 {                     return null;                 }                 else                 {                     return obj;                 }             }             catch (System.Data.SqlClient.SqlException e)             {                 connection.Close();                 throw new Exception(e.Message);             }         }      

        //执行查询语句，返回SqlDataReader.         public static SqlDataReader ExecuteReader(string strSQL)         {             SqlConnection connection = new SqlConnection(connectionString);             SqlCommand cmd = new SqlCommand(strSQL, connection);

            try             {                 connection.Open();                 SqlDataReader myReader = cmd.ExecuteReader();                 return myReader;             }             catch (System.Data.SqlClient.SqlException e)             {                 throw new Exception(e.Message);             }

         }

         //执行查询语句，返回DataSet.         public static DataSet Query(string SQLString)         {             SqlConnection connection = new SqlConnection(connectionString);                        DataSet ds = new DataSet();             try             {                 connection.Open();                 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);                 command.Fill(ds, "ds");             }             catch (System.Data.SqlClient.SqlException ex)             {                 throw new Exception(ex.Message);             }             return ds;                     }

        //参数为字段名和表名，返回最大值         public static int GetMaxID(string FieldName, string TableName)         {             string strsql = "select max (" + FieldName + ") from " + TableName;             object obj = GetSingle(strsql);             if (obj == null)             {                 return 1;             }             else             {                 return int.Parse(obj.ToString());             }         }

        //构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)，参数为数据库连接、存储过程名、存储过程参数。         public static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)         {             SqlCommand command = new SqlCommand(storedProcName, connection);             command.CommandType = CommandType.StoredProcedure;             foreach (SqlParameter parameter in parameters)             {                 command.Parameters.Add(parameter);             }             return command;         }

        //创建 SqlCommand 对象实例(用来返回一个整数值),参数为数据库连接、存储过程名、存储过程参数。         public static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)         {             SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);             command.Parameters.Add(new SqlParameter("ReturnValue",                 SqlDbType.Int, 4, ParameterDirection.ReturnValue,                 false, 0, 0, string.Empty, DataRowVersion.Default, null));             return command;         }

        //执行存储过程，返回SqlDataReader         public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)         {             SqlConnection connection = new SqlConnection(connectionString);             SqlDataReader returnReader;             connection.Open();             SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);             command.CommandType = CommandType.StoredProcedure;             returnReader = command.ExecuteReader();             return returnReader;         }

        //执行存储过程，返回结果集         public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)         {             SqlConnection connection = new SqlConnection(connectionString);                       DataSet dataSet = new DataSet();             connection.Open();             SqlDataAdapter sqlDA = new SqlDataAdapter();             sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);             sqlDA.Fill(dataSet, tableName);             connection.Close();             return dataSet;                     }

        //执行存储过程，返回影响的行数.         public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)         {             SqlConnection connection = new SqlConnection(connectionString);             int result;             connection.Open();             SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);             rowsAffected = command.ExecuteNonQuery();             result = (int)command.Parameters["ReturnValue"].Value;             //Connection.Close();             return result;                     }         //public int count(string sqls)         //{         //    System.Data.SqlClient.SqlConnection sqlf = this.createConnection();         //    System.Data.SqlClient.SqlCommand cmdf = new SqlCommand(sqls, sqlf);         //    sqlf.Open();

        //    int num = Convert.ToInt32(cmdf.ExecuteScalar());         //    if (num > 0)         //    {         //        sqlf.Close();         //        return num;         //    }         //    else         //    {         //        sqlf.Close();         //        return 0;

        //    }

        //} //返回执行结果的总数

    } }