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;
}
}
}