一:今天做了一个简单的sqlhelper,还有调用,将今天学的内容总结一下,解决方案如下:
二:对应的sqlhelper的内容:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
namespace UsersDAL
{
public class Class1
{
//返回受影响的行数
// static string constr = "server=.;database=OneDb;uid=sa;pwd=123123";
//使用app.config
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
//public int ExecuteNnonQuery(string sql)
//{
//string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
// using(SqlConnection conn=new SqlConnection(constr))
// {
// conn.Open();
// using(SqlCommand cmd=conn.CreateCommand())
// {
// cmd.CommandText = sql;
// return cmd.ExecuteNonQuery();
// }
// }
//}
//使用了长度可变参数
public int ExecuteNnonQuery(string sql,params SqlParameter[] parameters)
{
//string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
//遍历参数
//foreach(SqlParameter parame in parameters)
//{
// cmd.Parameters.Add(parame);
//}
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 返回首行首列
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public object ExecuteScalar(string sql,params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteScalar();
}
}
}
/// <summary>
/// 返回一个表
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
//public DataTable ExecuteDataTable(string sql,SqlParameter[] parameters)
//{
// using (SqlConnection conn = new SqlConnection(constr))
// {
// conn.Open();
// using (SqlCommand cmd = conn.CreateCommand())
// {
// cmd.CommandText = sql;
// cmd.Parameters.AddRange(parameters);
// SqlDataAdapter sda = new SqlDataAdapter(cmd);
// DataSet ds = new DataSet();
// sda.Fill(ds);
// return ds.Tables[0];
// }
// }
//}
//使用了长度可变参数
public DataTable ExecuteDataTable(string sql,params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds.Tables[0];
}
}
}
}
}
三:winform应用程序对sqlhelper的调用:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
namespace UsersDAL
{
public class Class1
{
//返回受影响的行数
// static string constr = "server=.;database=OneDb;uid=sa;pwd=123123";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
//public int ExecuteNnonQuery(string sql)
//{
//string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
// using(SqlConnection conn=new SqlConnection(constr))
// {
// conn.Open();
// using(SqlCommand cmd=conn.CreateCommand())
// {
// cmd.CommandText = sql;
// return cmd.ExecuteNonQuery();
// }
// }
//}
public int ExecuteNnonQuery(string sql,params SqlParameter[] parameters)
{
//string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
//方法一
//foreach(SqlParameter parame in parameters)
//{
// cmd.Parameters.Add(parame);
//}
//方法二
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 返回首行首列
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public object ExecuteScalar(string sql,params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteScalar();
}
}
}
/// <summary>
/// 返回一个表
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
//public DataTable ExecuteDataTable(string sql,SqlParameter[] parameters)
//{
// using (SqlConnection conn = new SqlConnection(constr))
// {
// conn.Open();
// using (SqlCommand cmd = conn.CreateCommand())
// {
// cmd.CommandText = sql;
// cmd.Parameters.AddRange(parameters);
// SqlDataAdapter sda = new SqlDataAdapter(cmd);
// DataSet ds = new DataSet();
// sda.Fill(ds);
// return ds.Tables[0];
// }
// }
//}
//使用了长度可变参数
public DataTable ExecuteDataTable(string sql,params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds.Tables[0];
}
}
}
}
}