ASP. NET simple 3-tier database connection read operation

Simple only Dal,model and web

Userdal is to read the database and convert the read result set into generics:

Using system;using system.collections.generic;using system.data;using system.data.sqlclient;using System.Linq;using System.reflection;using system.text;namespace andu.dal{public class Userdal {//public list<t> GetAllL  Istbyid<t> (String connstr, string sqlstr, int id) where T:model.basemodel//{//Sqlstr + = "where        Id= "+ ID; using (SqlConnection conn = new SqlConnection (CONNSTR))//{//using (SqlDataAdapter SDA =        New SqlDataAdapter (SQLSTR, conn))//{//DataSet ds = new DataSet (); Sda.        Fill (DS);        Return datasettolist<t> (ds, 0); }//}//}//public list<t> getalllist<t> (String connstr, string sqlstr) whe Re T:model.basemodel//{//using (SqlConnection conn=new SqlConnection (CONNSTR))//{/ /using (SqlDataAdapter sda=new sqldataadapteR (Sqlstr,conn))//{//DataSet ds=new DataSet (); Sda.        Fill (DS);        Return datasettolist<t> (ds, 0); }//}//} public list<t> getalllistbyid<t> (string connstr, String sqlstr,in            T ID) where T:model.basemodel {sqlstr + = "where id=" + ID; using (SqlConnection conn = new SqlConnection (connstr)) {SqlCommand command = new SqlCommand (Sq                LSTR, conn); Conn.                Open (); SqlDataReader Read = command.                ExecuteReader ();                DataTable dt = new DataTable (); Dt.                Load (read);            return datatabletolist<t> (DT);        }} public list<t> getalllist<t> (string connstr, String sqlstr) where T:model.basemodel {using (SqlConnection conn = new SqlConnection (connstr)) {SqlCommand command = new SqlCommand (SQLSTR, conn); Conn.                Open (); SqlDataReader Read = command.                ExecuteReader ();                DataTable dt = new DataTable (); Dt.                Load (read);                            return datatabletolist<t> (DT); }} private list<t> datatabletolist<t> (DataTable dt) {if (dt = = NULL | | dt.            Rows.Count = = 0) {return null;            } list<t> List = new list<t> ();            T entity = default (t); foreach (DataRow dr in Dt.                Rows) {entity = activator.createinstance<t> (); Propertyinfo[] PiS = entity. GetType ().                GetProperties (); foreach (PropertyInfo pi in PiS) {if (dt. Columns.contains (pi. Name) {if (!PI).                CanWrite) {continue;        } if (Dr[pi. Name] = DBNull.Value) {Type t = pi.                            PropertyType; if (T.fullname = = "System.Guid") {pi. SetValue (Entity, Guid.parse (DR[PI. Name].                            ToString ()), NULL); } else {pi. SetValue (Entity, Dr[pi.                            Name], NULL); }}}} list.            ADD (entity);        } return list; } public list<t> datasettolist<t> (DataSet DataSet, int p) {//Confirm parameter valid if (d Ataset = = NULL | | DataSet. Tables.count <= 0 | |            P < 0) {return null; } DataTable dt = DataSet.            TABLES[P];          list<t> list = new list<t> ();  for (int i = 0; i < dt. Rows.Count;                i++) {//Create generic object T _t = activator.createinstance<t> (); Gets all properties of the object propertyinfo[] PropertyInfo = _t.gettype ().                GetProperties (); When the property and name are the same, assign a value of for (int j = 0; j < dt. Columns.count;                        J + +) {foreach (PropertyInfo info in PropertyInfo) { if (dt. COLUMNS[J]. Columnname.toupper (). Equals (info. Name.toupper ())) {if (dt). ROWS[I][J]! = DBNull.Value) {info. SetValue (_t, dt.                            ROWS[I][J], NULL); } else {info.                            SetValue (_t, NULL, NULL);                        } break;            }                    }                }    List.            ADD (_t);        } return list; }    }}

The model layer is the database entity class, generated according to the database:

Basemodel:

Using system;using system.collections.generic;using system.linq;using system.text;namespace Andu.Model{public   Class Basemodel    {public       Basemodel () {} public        int ID {get; set;}}    }

Usermodel:

Using System;namespace andu.model{///<summary>user Table entity class///one or two//creation time: 2015-12-14 08:53:12///</summary>[ Serializable]public class Usermodel:basemodel{public Usermodel () {}private string _name;///<summary>/////< /summary>public string name{set{_name=value;} Get{return _name;}} private string _account;///<summary>/////</summary>public string account{set{_account=value;} Get{return _account;}} private string _password;///<summary>/////</summary>public string password{set{_password=value;} Get{return _password;}} private string _email;///<summary>/////</summary>public string email{set{_email=value;} Get{return _email;}} private string _mobile;///<summary>/////</summary>public string mobile{set{_mobile=value;} Get{return _mobile;}} private int _companyid;///<summary>//////</summary>public int companyid{set{_companyid=value;} Get{return _companyid;}} private String _companyname;///<summary>/////</summary>public string companyname{set{_companyname=value;} Get{return _companyname;}} private int _state;///<summary> user status 0 normal 1 Freeze 2 Delete/////</summary>public int state{set{_state=value;} Get{return _state;}} private int _usertype;///<summary> user Type 1 Normal User 2 admin 4 Super Admin////</summary>public int usertype{set{_usertyp E=value;} Get{return _usertype;}} Private datetime _lastlogintime;///<summary>/////</summary>public DateTime lastlogintime{set{_ Lastlogintime=value;} Get{return _lastlogintime;}} Private datetime _createtime;///<summary>/////</summary>public DateTime createtime{set{_createtime= Value;} Get{return _createtime;}} private int _creatorid;///<summary>//////</summary>public int creatorid{set{_creatorid=value;} Get{return _creatorid;}} private int _lastmodifierid;///<summary>/////</summary>public int lastmodifierid{set{_LastModifierId= Value;} Get{return _lastmodifierid;}} Private DatETime _lastmodifytime;///<summary>/////</summary>public DateTime lastmodifytime{set{_LastModifyTime= Value;} Get{return _lastmodifytime;}}}

The Usermodel of the door is generated automatically with three layers, automatically generated according to the database

Web. config:

<?xml version= "1.0" encoding= "Utf-8"?><!--  For more information about how to configure an ASP. NET application, go to  http://go.microsoft.com/ fwlink/? linkid=169433  --><configuration>  <connectionStrings>    <add name= "Conn" connectionstring= "Data source=.;i Nitial catalog=zuoye01; User Id=sa; Password=sa "/>  </connectionStrings>    <system.web>      <compilation debug=" true " targetframework= "4.0"/>    </system.web></configuration>

Front Page Call:

WebForm1.aspx

<%@ page language= "C #" autoeventwireup= "true" codebehind= "WebForm1.aspx.cs" inherits= "Web.webform1"%><! DOCTYPE html>Using system;using system.collections.generic;using system.linq;using system.web;using System.Web.UI;using System.web.ui.webcontrols;using andu.model;using andu.dal;using system.data.sqlclient;using System.Configuration;        namespace web{public partial class WebForm1:System.Web.UI.Page {userdal Userdal = new Userdal (); protected void Page_Load (object sender, EventArgs e) {String conn = Configurationmanager.connectionstri ngs["Conn"].            ToString ();            String sql = "SELECT * FROM [User]"; Get all Repeater1.datasource = Userdal.            getalllist<usermodel> (conn, SQL);            Repeater1.databind (); Get//repeater1.datasource = Userdal by ID.            getalllistbyid<usermodel> (conn, SQL, 11);            Repeater1.databind (); List<usermodel> Usermodel = Userdal.            getalllist<usermodel> (conn, SQL); Usertype from small to large sort//usermodel. Sort (x, y) = = X.usertype.compareto (y.usertype)); Usertype from large to small sort//usermodel.            Sort ((x, y) = Y.usertype.compareto (X.usertype));            Repeater1.datasource = Usermodel;            Repeater1.databind ();            int Maxu = 0; Maxu = Usermodel.            Max (x = X.usertype); Label1.Text = "Usertype maximum value in user table is:" + Maxu.            ToString (); Maxu = Usermodel.            Min (x = X.usertype); Label1.Text = "The usertype minimum value in the user table is:" + Maxu.            ToString (); Double Pingjun = Usermodel.            Average (x = X.usertype); Label1.Text = "The usertype average in the user table is:" + Pingjun.            ToString ("0.00"); Maxu = Usermodel.            Sum (x = X.usertype); Label1.Text = "User table usertype and yes:" + Maxu.        ToString (); }    }}

　　

ASP. NET simple 3-tier database connection read operation