using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
namespace Stu.Model
{
public class StuModel
{
public int Id { get; set; }
public string Name { get; set; }
public bool? Sex { get; set; }
public DateTime? AddTime { get; set; }
}
}
/
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.ComponentModel;
using System.Collections.Generic;
using System.Data.SqlClient;
namespace Stu.DAL
{
using Stu.Model;
public class StuDao
{
private string ConnStr = @"Data Source=********\SQL2008; Initial Catalog=Stu;Integrated Security=True";
public void Add(StuModel entity)
{
string cmdText = "Insert Into Stu(Id,Name,Sex,AddTime) Values(@Id,@Name,@Sex,@AddTime)";
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand(cmdText, conn);
DaoHelper.AddParameter(cmd, "@Id", entity.Id);
DaoHelper.AddParameter(cmd, "@Name", entity.Name);
DaoHelper.AddParameter(cmd, "@Sex", entity.Sex);
DaoHelper.AddParameter(cmd, "@AddTime", entity.AddTime);
conn.Open();
cmd.ExecuteNonQuery();
}
}
public int MaxId()
{
string cmdText = "Select Max(Id) from Stu";
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand(cmdText, conn);
conn.Open();
var obj = cmd.ExecuteScalar();
if (obj == null || obj is DBNull) return 1;
return (int)obj + 1;
}
}
public void Update(StuModel entity)
{
string cmdText = @"
Update Stu Set Name=@Name,Sex=@Sex,AddTime=@AddTime
Where Id=@Id
";
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand(cmdText, conn);
conn.Open();
DaoHelper.AddParameter(cmd, "@Id", entity.Id);
DaoHelper.AddParameter(cmd, "@Name", entity.Name);
DaoHelper.AddParameter(cmd, "@Sex", entity.Sex);
DaoHelper.AddParameter(cmd, "@AddTime", entity.AddTime);
cmd.ExecuteNonQuery();
}
}
public void Del(int id)
{
//
string cmdText = "Delete From stu Where Id=@Id ";
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.Parameters.Add(new SqlParameter("@Id", id));
conn.Open();
cmd.ExecuteNonQuery();
}
}
public StuModel Get(int id)
{
string cmdText = "Select * From stu Where Id=@Id ";
StuModel model = null;
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand(cmdText, conn);
DaoHelper.AddParameter(cmd, "@Id", id);
conn.Open();
using (IDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
model = new StuModel();
model.Id = (int)reader["Id"];
model.Name = DaoHelper.GetValueFromReader<string>(reader, "Name");
model.Sex = DaoHelper.GetValueFromReader<bool?>(reader, "Sex");
model.AddTime = DaoHelper.GetValueFromReader<DateTime?>(reader, "AddTime");
}
}
}
return model;
}
public List<StuModel> GetAll()
{
var list=new List<StuModel>();
string cmdText = "Select * From Stu Order By Id";
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand(cmdText, conn);
conn.Open();
using (IDataReader reader = cmd.ExecuteReader())
{
while(reader.Read())
{
var model = new StuModel();
model.Id = (int)reader["Id"];
model.Name = DaoHelper.GetValueFromReader<string>(reader, "Name");
model.Sex = DaoHelper.GetValueFromReader<bool?>(reader, "Sex");
model.AddTime = DaoHelper.GetValueFromReader<DateTime?>(reader, "AddTime");
list.Add(model);
}
}
}
return list;
}
}
}
/
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
namespace Stu
{
using Stu.Model;
using Stu.DAL;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
var dao = new StuDao();
var entity = new StuModel();
entity.Name = null;
entity.Sex = null;
entity.AddTime = null;
entity.Id = dao.MaxId();
dao.Add(entity);
//dao.Update(entity);
var list = dao.GetAll();
foreach (var item in list)
{
Response.Write(string.Format("Id:{0},Name:{1} <a href='StuDetai.aspx?id={0}' target='_blank'>查看</a><br/>",item.Id,item.Name ));
}
//model.Name = "张三";
//dao.Update(model);
}
protected void Button2_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(@"Data Source=**********\SQL2008; Initial Catalog=Stu;Integrated Security=True");
SqlCommand cmd = new SqlCommand("UPADATA Stu SET Id=2 ",conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
读可空字段
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.ComponentModel;
namespace Stu.DAL
{
public class DaoHelper
{
public static TResulte GetValueFromReader<TResulte>(IDataReader reader, string field)
{
var obj = reader[field];
if (obj == null || obj is DBNull)
{
return default(TResulte);
}
return (TResulte)obj;
}
public static void AddParameter(SqlCommand cmd, string parameterName, object value)
{
SqlParameter parameter = new SqlParameter(parameterName, value);
if (value == null)
{
parameter.IsNullable = true;
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
/