使用SqlHelper
时间:2010-09-25 来源:赖顺生
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace TestSQL_Project
{
public class DBPersonSqlHelper
{
private static readonly string connString = "Data Source=(local);Database=MyTest;Uid=sa;Pwd=123456";
/// <summary>
/// 添加用户
/// </summary>
/// <param name="person">要添加的用户</param>
public static void AddPerson(Person person)
{
string insertString = "insert into T_Person(p_name,p_age) values(@name,@age)";
SqlParameter[] cmdParameter = {
new SqlParameter("@name", SqlDbType.VarChar),
new SqlParameter("@age", SqlDbType.Int)
};
cmdParameter[0].Value = person.Name;
cmdParameter[1].Value = person.Age;
try
{
SqlHelper.ExecuteNonQuery(new SqlConnection(connString), CommandType.Text, insertString, cmdParameter);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
/// <summary>
/// 通过用户ID删除用户
/// </summary>
/// <param name="id">要删除的用户的ID</param>
public static void DeletePersonByID(int id)
{
string deleteString = "delete from T_Person where p_id = @id";
SqlParameter[] cmdParameter = {
new SqlParameter("@id", SqlDbType.Int)
};
cmdParameter[0].Value = id;
try
{
SqlHelper.ExecuteNonQuery(new SqlConnection(connString), CommandType.Text, deleteString, cmdParameter);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
/// <summary>
/// 更新用户
/// </summary>
/// <param name="person">要更新的用户</param>
public static void UpdatePerson(Person person)
{
string updateString = "update T_Person set p_name = @name,p_age = @age where p_id = @id";
SqlParameter[] sqlParameter = {
new SqlParameter("@id",SqlDbType.Int),
new SqlParameter("@name",SqlDbType.VarChar),
new SqlParameter("@age", SqlDbType.Int)
};
sqlParameter[0].Value = person.Id;
sqlParameter[1].Value = person.Name;
sqlParameter[2].Value = person.Age;
try
{
SqlHelper.ExecuteNonQuery(new SqlConnection(connString), CommandType.Text, updateString, sqlParameter);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
/// <summary>
/// 通过id取得用户
/// </summary>
/// <param name="id">要取得的用户的id</param>
/// <returns>取得的用户</returns>
public static Person GetPersonByID(int id)
{
if (!PersonIsExistById(id))
{
return null;
}
Person person = new Person();
string selectString = "select * from T_Person where p_id = @id";
SqlParameter[] sqlPara = {
new SqlParameter("@id",SqlDbType.Int)
};
sqlPara[0].Value = id;
SqlDataReader dr = SqlHelper.ExecuteReader(connString, CommandType.Text, selectString, sqlPara);
try
{
while (dr.Read())
{
person.Id = Convert.ToInt32(dr["p_id"]);
person.Name = dr["p_name"].ToString();
person.Age = Convert.ToInt32(dr["p_age"]);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
return null;
}
return person;
}
/// <summary>
/// 取得最大的id
/// </summary>
/// <returns>如果返回-1表示数据库中没有数据,否则返回的就是最大的id</returns>
public static int GetMaxPersonID()
{
string selectString = "select max(p_id) as maxID from T_Person";
object obj = null;
obj = SqlHelper.ExecuteScalar(new SqlConnection(connString), CommandType.Text, selectString, null);
if (!TableHasRecord())
{
return -1;
}
else
{
return Convert.ToInt32(obj);
}
}
/// <summary>
/// 根据id判断用户是否存在
/// </summary>
/// <param name="id">要判断的用户的id</param>
/// <returns>返回true表示该用户存在,返回false表示该用户不存在</returns>
public static bool PersonIsExistById(int id)
{
string selectString = "select * from T_Person where p_id = @id";
SqlParameter[] sqlPara = {
new SqlParameter("@id",SqlDbType.Int)
};
sqlPara[0].Value = id;
SqlDataReader dr = SqlHelper.ExecuteReader(connString, CommandType.Text, selectString, sqlPara);
while (dr.Read())
{
return true;
}
return false;
}
/// <summary>
/// 判断表中是否有数据
/// </summary>
/// <returns>返回true表示有数据,返回false表示没有数据</returns>
public static bool TableHasRecord()
{
string selectString = "select * from T_Person";
SqlDataReader dr = SqlHelper.ExecuteReader(connString, CommandType.Text, selectString, null);
while (dr.Read())
{
return true;
}
return false;
}
/// <summary>
/// 取得所有用户
/// </summary>
/// <returns>所有用户的集合</returns>
public static List<Person> GetManyPersons()
{
string selectString = "select * from T_Person";
List<Person> lstPerson = new List<Person>();
Person person = null;
DataSet ds = SqlHelper.ExecuteDataSet(connString, CommandType.Text, selectString, null);
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
person = new Person();
person.Id = Convert.ToInt32(ds.Tables[0].Rows[0]["p_id"].ToString());
person.Name = ds.Tables[0].Rows[0]["p_name"].ToString();
person.Age = Convert.ToInt32(ds.Tables[0].Rows[0]["p_age"].ToString());
lstPerson.Add(person);
}
}
return lstPerson;
}
}
}
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace TestSQL_Project
{
public class DBPersonSqlHelper
{
private static readonly string connString = "Data Source=(local);Database=MyTest;Uid=sa;Pwd=123456";
/// <summary>
/// 添加用户
/// </summary>
/// <param name="person">要添加的用户</param>
public static void AddPerson(Person person)
{
string insertString = "insert into T_Person(p_name,p_age) values(@name,@age)";
SqlParameter[] cmdParameter = {
new SqlParameter("@name", SqlDbType.VarChar),
new SqlParameter("@age", SqlDbType.Int)
};
cmdParameter[0].Value = person.Name;
cmdParameter[1].Value = person.Age;
try
{
SqlHelper.ExecuteNonQuery(new SqlConnection(connString), CommandType.Text, insertString, cmdParameter);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
/// <summary>
/// 通过用户ID删除用户
/// </summary>
/// <param name="id">要删除的用户的ID</param>
public static void DeletePersonByID(int id)
{
string deleteString = "delete from T_Person where p_id = @id";
SqlParameter[] cmdParameter = {
new SqlParameter("@id", SqlDbType.Int)
};
cmdParameter[0].Value = id;
try
{
SqlHelper.ExecuteNonQuery(new SqlConnection(connString), CommandType.Text, deleteString, cmdParameter);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
/// <summary>
/// 更新用户
/// </summary>
/// <param name="person">要更新的用户</param>
public static void UpdatePerson(Person person)
{
string updateString = "update T_Person set p_name = @name,p_age = @age where p_id = @id";
SqlParameter[] sqlParameter = {
new SqlParameter("@id",SqlDbType.Int),
new SqlParameter("@name",SqlDbType.VarChar),
new SqlParameter("@age", SqlDbType.Int)
};
sqlParameter[0].Value = person.Id;
sqlParameter[1].Value = person.Name;
sqlParameter[2].Value = person.Age;
try
{
SqlHelper.ExecuteNonQuery(new SqlConnection(connString), CommandType.Text, updateString, sqlParameter);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
/// <summary>
/// 通过id取得用户
/// </summary>
/// <param name="id">要取得的用户的id</param>
/// <returns>取得的用户</returns>
public static Person GetPersonByID(int id)
{
if (!PersonIsExistById(id))
{
return null;
}
Person person = new Person();
string selectString = "select * from T_Person where p_id = @id";
SqlParameter[] sqlPara = {
new SqlParameter("@id",SqlDbType.Int)
};
sqlPara[0].Value = id;
SqlDataReader dr = SqlHelper.ExecuteReader(connString, CommandType.Text, selectString, sqlPara);
try
{
while (dr.Read())
{
person.Id = Convert.ToInt32(dr["p_id"]);
person.Name = dr["p_name"].ToString();
person.Age = Convert.ToInt32(dr["p_age"]);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
return null;
}
return person;
}
/// <summary>
/// 取得最大的id
/// </summary>
/// <returns>如果返回-1表示数据库中没有数据,否则返回的就是最大的id</returns>
public static int GetMaxPersonID()
{
string selectString = "select max(p_id) as maxID from T_Person";
object obj = null;
obj = SqlHelper.ExecuteScalar(new SqlConnection(connString), CommandType.Text, selectString, null);
if (!TableHasRecord())
{
return -1;
}
else
{
return Convert.ToInt32(obj);
}
}
/// <summary>
/// 根据id判断用户是否存在
/// </summary>
/// <param name="id">要判断的用户的id</param>
/// <returns>返回true表示该用户存在,返回false表示该用户不存在</returns>
public static bool PersonIsExistById(int id)
{
string selectString = "select * from T_Person where p_id = @id";
SqlParameter[] sqlPara = {
new SqlParameter("@id",SqlDbType.Int)
};
sqlPara[0].Value = id;
SqlDataReader dr = SqlHelper.ExecuteReader(connString, CommandType.Text, selectString, sqlPara);
while (dr.Read())
{
return true;
}
return false;
}
/// <summary>
/// 判断表中是否有数据
/// </summary>
/// <returns>返回true表示有数据,返回false表示没有数据</returns>
public static bool TableHasRecord()
{
string selectString = "select * from T_Person";
SqlDataReader dr = SqlHelper.ExecuteReader(connString, CommandType.Text, selectString, null);
while (dr.Read())
{
return true;
}
return false;
}
/// <summary>
/// 取得所有用户
/// </summary>
/// <returns>所有用户的集合</returns>
public static List<Person> GetManyPersons()
{
string selectString = "select * from T_Person";
List<Person> lstPerson = new List<Person>();
Person person = null;
DataSet ds = SqlHelper.ExecuteDataSet(connString, CommandType.Text, selectString, null);
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
person = new Person();
person.Id = Convert.ToInt32(ds.Tables[0].Rows[0]["p_id"].ToString());
person.Name = ds.Tables[0].Rows[0]["p_name"].ToString();
person.Age = Convert.ToInt32(ds.Tables[0].Rows[0]["p_age"].ToString());
lstPerson.Add(person);
}
}
return lstPerson;
}
}
}
相关阅读 更多 +