Web.config
<connectionStrings>
<add name="connStr" connectionString="server=.;database=ProductDB;uid=sa;pwd=123456"/>
</connectionStrings>
极简版
public class DBHelper
{
public static string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
// 第一行第一列用dt.Rows[0][0]
public static DataTable GetDt(string sqlStr)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
DataTable dt = new DataTable();
SqlCommand comm = new SqlCommand(sqlStr, conn);
SqlDataAdapter dap = new SqlDataAdapter(comm);
dap.Fill(dt);
return dt;
}
}
public static int GetExecuteNonQuery(string sqlStr, CommandType ct, SqlParameter[] sqls)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
SqlCommand comm = new SqlCommand(sqlStr, conn);
if (sqls != null && sqls.Length > 0)
{
foreach (SqlParameter s in sqls)
{
comm.Parameters.Add(s);
}
}
comm.CommandType = ct;
conn.Open();
int i = comm.ExecuteNonQuery();
return i;
}
}
}
封装版
public class DBHelper
{
private static string connSql = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
public static Result GetExecuteNonQuerySql(string sqlStr)
{
return GetExecuteNonQuery(sqlStr, CommandType.Text, null);
}
public static Result GetExecuteNonQuerySql_Sqls(string sqlStr, SqlParameter[] sqls)
{
return GetExecuteNonQuery(sqlStr, CommandType.Text, sqls);
}
public static Result GetExecuteNonQueryProc(string sqlStr)
{
return GetExecuteNonQuery(sqlStr, CommandType.StoredProcedure, null);
}
public static Result GetExecuteNonQueryProc_Sqls(string sqlStr, SqlParameter[] sqls)
{
return GetExecuteNonQuery(sqlStr, CommandType.StoredProcedure, sqls);
}
private static Result GetExecuteNonQuery(string sqlStr, CommandType ct, SqlParameter[] sqls)
{
using (SqlConnection conn = new SqlConnection(connSql))
{
try
{
SqlCommand comm = new SqlCommand(sqlStr, conn);
if (sqls != null && sqls.Length > 0)
{
foreach (SqlParameter s in sqls)
{
comm.Parameters.Add(s);
}
}
comm.CommandType = ct;
conn.Open();
int i = comm.ExecuteNonQuery();
return new Result(i);
}
catch (Exception ex)
{
return new Result(ex.Message, -2);
}
finally
{
conn.Close();
}
}
}
public static Result GetSqlDataAdapterSql(string sqlStr)
{
return GetSqlDataAdapter(sqlStr, CommandType.Text, null);
}
public static Result GetSqlDataAdapterSql_Sqls(string sqlStr, SqlParameter[] sqls)
{
return GetSqlDataAdapter(sqlStr, CommandType.Text, sqls);
}
public static Result GetSqlDataAdapterProc(string sqlStr)
{
return GetSqlDataAdapter(sqlStr, CommandType.StoredProcedure, null);
}
public static Result GetSqlDataAdapterProc_Sqls(string sqlStr, SqlParameter[] sqls)
{
return GetSqlDataAdapter(sqlStr, CommandType.StoredProcedure, sqls);
}
private static Result GetSqlDataAdapter(string sqlStr, CommandType ct, SqlParameter[] sqls)
{
using (SqlConnection conn = new SqlConnection(connSql))
{
try
{
SqlCommand comm = new SqlCommand(sqlStr, conn);
if (sqls != null && sqls.Length > 0)
{
foreach (SqlParameter s in sqls)
{
comm.Parameters.Add(s);
}
}
DataTable dt = new DataTable();
SqlDataAdapter dap = new SqlDataAdapter(comm);
comm.CommandType = ct;
dap.Fill(dt);
return new Result(dt);
}
catch (Exception ex)
{
DataTable dt = null;
return new Result(ex.Message, dt);
}
finally
{
conn.Close();
}
}
}
}
public class Result
{
//标识
public int Sign { get; set; }
//提示
public string Mess { get; set; }
//数据表
public DataTable TD { get; set; }
public Result(int sign)
{
this.Sign = sign;
}
public Result(string mess, int sign)
{
this.Sign = sign;
this.Mess = mess;
}
public Result(DataTable table)
{
this.TD = table;
}
public Result(string mess, DataTable table)
{
this.TD = table;
this.Mess = mess;
}
}
{
//标识
public int Sign { get; set; }
//提示
public string Mess { get; set; }
//数据表
public DataTable TD { get; set; }
public Result(int sign)
{
this.Sign = sign;
}
public Result(string mess, int sign)
{
this.Sign = sign;
this.Mess = mess;
}
public Result(DataTable table)
{
this.TD = table;
}
public Result(string mess, DataTable table)
{
this.TD = table;
this.Mess = mess;
}
}
DBHelper返回的DataTable的接收与使用
.Aspx
<table cellpadding="0" cellspacing="0">
<tr>
<th colspan="5">产品类别编号<a href="Add.aspx" id="add">添加类别</a></th>
</tr>
<tr>
<th>类别编号</th>
<th>类别名称</th>
<th>上级类别</th>
<th colspan="2">操作</th>
</tr>
<asp:Repeater ID="Repeater_init" runat="server">
<ItemTemplate>
<tr>
<td><%# Eval("ClassifyId") %></td>
<td><%# Eval("ClassifyName") %></td>
<td><%# Eval("ParentId") %></td>
<td><a href="Update.aspx?id=<%# Eval("ClassifyId") %>">编辑</a></td>
<td><a href="Delelte.aspx?id=<%# Eval("ClassifyId") %>">删除</a></td>
</tr>
</ItemTemplate>
</asp:Repeater>
</table>
<th colspan="5">产品类别编号<a href="Add.aspx" id="add">添加类别</a></th>
</tr>
<tr>
<th>类别编号</th>
<th>类别名称</th>
<th>上级类别</th>
<th colspan="2">操作</th>
</tr>
<asp:Repeater ID="Repeater_init" runat="server">
<ItemTemplate>
<tr>
<td><%# Eval("ClassifyId") %></td>
<td><%# Eval("ClassifyName") %></td>
<td><%# Eval("ParentId") %></td>
<td><a href="Update.aspx?id=<%# Eval("ClassifyId") %>">编辑</a></td>
<td><a href="Delelte.aspx?id=<%# Eval("ClassifyId") %>">删除</a></td>
</tr>
</ItemTemplate>
</asp:Repeater>
</table>
Aspx后台
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Result re = DBHelper.GetSqlDataAdapterSql("select * from v_User ");
Repeater_init.DataSource = re.TD;
Repeater_init.DataBind();
}
}
DBHelper返回的DataTable的接收与使用
.Cshtml razor语法
<table class="table table-bordered table-striped table-hover table-condensed">
<th>编号</th>
<th>姓名</th>
<th>密码</th>
<th>电话</th>
<th>Email</th>
<th>部门</th>
<th>操作</th>
</tr>
@foreach (User item in ViewBag.userList)
{
<tr>
<td>@item.Us_Id</td>
<td>@item.Us_Name</td>
<td>@item.Us_Pwd</td>
<td>@item.Us_Phone</td>
<td>@item.Us_Email</td>
<td>@item.De_Name</td>
<td>
<a href="/temp/edit?id=@item.Us_Id">编辑</a>
<a href="/temp/delete?id=@item.Us_Id">删除</a>
</td>
</tr>
}
</table>
MVC-controller
public ActionResult Index()
{
Result re = DBHelper.GetSqlDataAdapterSql("select * from v_User ");
// 把DataTable转换为IList<UserInfo>
IList<User> users = ModelConvertHelper<User>.ConvertToModel(re.TD);
ViewBag.userList = users;
return View();
}
转换工具类
/// <summary>/// 将DataTable转成Model
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public class ModelConvertHelper<T> where T : new()
{
public static List<T> ConvertToModel(DataTable dt)
{
// 定义集合
List<T> ts = new List<T>();
// 获得此模型的类型
Type type = typeof(T);
string tempName = "";
foreach (DataRow dr in dt.Rows)
{
T t = new T();
// 获得此模型的公共属性
PropertyInfo[] propertys = t.GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
tempName = pi.Name;
// 检查DataTable是否包含此列
if (dt.Columns.Contains(tempName))
{
// 判断此属性是否有Setter
if (!pi.CanWrite)
continue;
object value = dr[tempName];
if (value != DBNull.Value)
{
//pi.SetValue(t, value, null);
pi.SetValue(t, Convert.ChangeType(value, pi.PropertyType, CultureInfo.CurrentCulture), null);
}
}
}
ts.Add(t);
}
return ts;
}
}
List<Object> types = new List<object>();
types.Add(new { ID = item.ID, Name = item.Name, ExpireEndDate = Convert.ToDateTime(startDate).AddDays(1) });
dynamic temp = types[i];
int tempId = temp.ID;
int tempId = temp.ID;
DateTime tempDate = temp.ExpireEndDate;