ado.net

 

SqlHelper:

  public class SqlHelper
    {
        //connect string
        public static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ConnectionString;

        private static void PrepareCommand(SqlConnection conn,SqlCommand cmd,CommandType ct,string CommandString, SqlParameter[] param)
        {
            //if close,open 
            if (conn.State != ConnectionState.Open)
                conn.Open();

            //bind sqlcommand
            cmd.Connection = conn;
            cmd.CommandText = CommandString;
            cmd.CommandType = ct;

            //if have param
            if (param != null)
            {
                foreach (SqlParameter pa in param) 
                {
                    cmd.Parameters.Add(pa);
                }
            }
        }

        public static object ExcuteScalar(string ConnectionS,CommandType ct,string CommandString,params SqlParameter[] param)
        {
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(ConnectionS))
            {
                PrepareCommand(conn, cmd, ct, CommandString, param);           
                object o = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return o;
            }
        }

        public static int ExcuteNonQuery(string ConnectionS, CommandType ct, string CommandString, params SqlParameter[] param)
        {
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(ConnectionS))
            {
                PrepareCommand(conn, cmd, ct, CommandString, param);
                int i = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return i;
            }
        }

        public static SqlDataReader ExcuteReader(string ConnectionS, CommandType ct, string CommandString, params SqlParameter[] param)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(ConnectionS);
            try
            {
                PrepareCommand(conn, cmd, ct, CommandString, param);
                SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return sdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }
    }

ExcuteSql:

  public class ExcuteSql
    {

        public bool InMessage(string Names, string Sex)
        {
            string sqlstr = "insert into Person(Names) values (@Names)";
            SqlParameter[] sqlparam = { 
                                      new SqlParameter("@Names",SqlDbType.NVarChar,50)                     
                                      };
            sqlparam[0].Value = Names;
            //if you want to use ExcuteScalar insert select @@identity to end of your sql
            int i = SqlHelper.ExcuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, sqlstr, sqlparam);
            if (i <= 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public bool UpMessage(string Names, int id)
        {
            string sqlstr = "update Person set Names=@Names where id=@id";
            SqlParameter[] sqlparam = { 
                                      new SqlParameter("@Names",SqlDbType.NVarChar,50),
                                      new SqlParameter("@id",SqlDbType.Int)       
                                      };
            sqlparam[0].Value = Names;
            sqlparam[1].Value = id;
            int i = SqlHelper.ExcuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, sqlstr, sqlparam);
            if (i <= 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public bool DelMessage(int id)
        {
            string sqlstr = "delete from Person where id=@id";
            SqlParameter[] sqlparam = { 
                                      new SqlParameter("@id",SqlDbType.Int)       
                                      };
            sqlparam[0].Value = id;
            int i = SqlHelper.ExcuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, sqlstr, sqlparam);
            if (i <= 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        public IList<ModelTable> GetMessage(int id)
        {
            string strsql = "select ID,Names,Sex,Adress,Tel from Person where id=@id order by ID desc";

            SqlParameter[] sqlparam = { 
                                    new SqlParameter("@ID",DbType.Int32)
                                      };
            sqlparam[0].Value = id;
            SqlDataReader dr = SqlHelper.ExcuteReader(SqlHelper.ConnectionString, CommandType.Text, strsql, sqlparam);
            IList<ModelTable> models = new List<ModelTable>();
            while (dr.Read())
            {
                ModelTable mt = new ModelTable(Convert.ToInt32(dr[0]), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString());
                models.Add(mt);
            }
            dr.Close();
            return models;
        }
        //Proc
        public bool InsertWithProc(string Names)
        {
            SqlParameter[] sqlparam = { 
                                          new SqlParameter("@Names",SqlDbType.NVarChar,50)
                                          
                                          };
            sqlparam[0].Value = Names;
            int i = SqlHelper.ExcuteNonQuery(SqlHelper.ConnectionString, CommandType.StoredProcedure, ProcEnum.InsertProc.ToString(), sqlparam);
            if (i <= 0)
            {
                return false;
            }
            else
            {

                return true;
            }

        }
    }