0311copy数据操作& 空字段处置

0311copy数据操作& 空字段处理

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

?

/////////////////////////////////////////////////////////////////