dbhelper.cs

简单聊天系统,自己做的,有数据库.多多支持,做的不好,请大家原谅.

using System;
using System.Collections.Generic;
using System.Text;

using System.Data.SqlClient;
using System.Data;
namespace NewChartDAL
{
   public  class DBHelper
    {
        //private static string connectionstring = "server=(local)\\APTECH;database=myBlog;integrated security=sspi";
        private static string connectionstring = "server=(local)\\SQLEXPRESS;database=chat;integrated security=sspi";
        public static int ExecuteCommand(string safeSql)
        {

            using (SqlConnection connection = new SqlConnection(connectionstring))
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand(safeSql, connection);
                int result = cmd.ExecuteNonQuery();
                return result;
            }

        }

        public static int ExecuteCommand(string sql, params SqlParameter[] values)
        {
            using (SqlConnection connection = new SqlConnection(connectionstring))
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand(sql, connection);
                cmd.Parameters.AddRange(values);
                return cmd.ExecuteNonQuery();
            }

        }

        public static string ReturnStringScalar(string safeSql)
        {
            using (SqlConnection connection = new SqlConnection(connectionstring))
            {
                SqlCommand cmd = new SqlCommand(safeSql, connection);
                connection.Open();
                string result = cmd.ExecuteScalar().ToString();
                return result;

            }
        }

        public static int GetScalar(string safeSql)
        {
            using (SqlConnection connection = new SqlConnection(connectionstring))
            {
                SqlCommand cmd = new SqlCommand(safeSql, connection);

                connection.Open();
                int result = Convert.ToInt32(cmd.ExecuteScalar());
                return result;
            }

        }


        public static int GetScalar(string sql, params SqlParameter[] values)
        {
            using (SqlConnection connection = new SqlConnection(connectionstring))
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand(sql, connection);
                cmd.Parameters.AddRange(values);
                int result = Convert.ToInt32(cmd.ExecuteScalar());
                return result;
            }

        }


        public static SqlDataReader GetReader(string safeSql)
        {
            SqlConnection connection = new SqlConnection(connectionstring);
            try
            {
                connection.Open();

                SqlCommand cmd = new SqlCommand(safeSql, connection);
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                return reader;
            }
            catch
            {
                connection.Close();
                throw;
            }
        }

       public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
       {
           SqlConnection connection = new SqlConnection(connectionstring);
           try
           {
               connection.Open();

               SqlCommand cmd = new SqlCommand(sql, connection);
               cmd.Parameters.AddRange(values);
               SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

               return reader;
           }
           catch
           {
               connection.Close();
               throw;
           }
       }
        public static DataTable GetDataSet(string safeSql)
        {
            using (SqlConnection connection = new SqlConnection(connectionstring))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = new SqlCommand(safeSql, connection);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                return ds.Tables[0];
            }
        }

        public static DataTable GetDataSet(string sql, params SqlParameter[] values)
        {
            using (SqlConnection connection = new SqlConnection(connectionstring))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = new SqlCommand(sql, connection);
                cmd.Parameters.AddRange(values);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                return ds.Tables[0];
            }
        }


        //执行事务处理SQL
        public static bool ExecTSQL(string[] strSql)
        {

            using (SqlConnection connection = new SqlConnection(connectionstring))
            {
                SqlTransaction trans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
                try
                {
                    connection.Open();
                    for (int i = 0; i < strSql.Length; i++)
                    {
                        if (strSql[i] == "" || strSql[i] == null) { continue; }
                        SqlCommand sqlCmd = connection.CreateCommand();
                        sqlCmd.Transaction = trans;
                        sqlCmd.CommandText = strSql[i];
                        sqlCmd.ExecuteNonQuery();
                    }
                    trans.Commit();
                    return true;
                }
                catch (Exception)
                {
                    trans.Rollback();
                    return false;
                }
            }



        }

        //执行存储过程
        public static bool ExecProc(string strProcName)
        {
            using (SqlConnection connection = new SqlConnection(connectionstring))
            {
                connection.Open();
                SqlCommand sqlCmd = connection.CreateCommand();
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = strProcName;

                sqlCmd.ExecuteNonQuery();
                return true;
            }
        }

        //执行带参存储过程
        public static void ExecProc(string strProcName, SqlParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionstring))
            {
                connection.Open();
                SqlCommand sqlCmd = connection.CreateCommand();
                sqlCmd.CommandText = strProcName; //声明存储过程名
                sqlCmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter parameter in parameters)
                {
                    sqlCmd.Parameters.Add(parameter);
                }

                sqlCmd.ExecuteNonQuery();
            }
        }
    }
}