Codesmith data layer template makes operations easier

 

<% @ Codetemplate Language = "C #" targetlanguage = "text" src = "" inherits = "" DEBUG = "false" Description = "template description here." %>
<% @ Assembly name = "schemaexplorer" %>

<% @ Assembly name = "system. Data" %>

<% @ Property name = "targettable" type = "schemaexplorer. tableschema" Category = "context" Description = "targettable that the object is based on." %>

<% @ Property name = "author" type = "system. String" default = "Xiao Changjian" Category = "context" Description = "the author for this procedure." %>

<% @ Property name = "namespace" type = "system. String" default = "MySpace" Category = "context" Description = "targettable that the object is based on." %>

<% @ Property name = "Description" type = "system. String" default = "" Category = "context" Description = "the description for this procedure." %>

<% @ Map Name = "csharpalias" src = "system-csharpalias" Description = "system to C # type map" %>

<% @ Import namespace = "schemaexplorer" %>

<% @ Import namespace = "system. Data" %>

/*----------------------------------------------------------------------

* File name: <% = gettablename (targettable) %> Dal. CS

*

* Create Author: <% = author %>

*

* Create datetime: <% = datetime. Now. tow.datestring () %>

*

* Description: <% = Description %>

*----------------------------------------------------------------------*/

Using system;

Using system. Collections. Generic;

Using system. text;

Using system. Data;

Using system. Data. sqlclient;

Using dbutilitys;

Namespace <% = namespace %>

{

Public class <% = gettablename (targettable) %> dal

{

Private Static readonly <% = gettablename (targettable) %> Dal _ classinstance = new <% = gettablename (targettable) %> Dal ();

Public static <% = gettablename (targettable) %> Dal classinstance {get {return _ classinstance ;}}

/// <Summary>

/// Add record

/// </Summary>

Public int insert _ <% = gettablename (targettable) %> (<% = gettablename (targettable) %> model instance)

{

String executesqlstr = "insert into <% = targettable. name %> (<% = getcolumnnamenopk (targettable) %>) values (<% = evaluatecolumnvaluenopk (targettable) %> )";

Sqlparameter [] para = new sqlparameter [] {

<% Int I = 0; foreach (columnschema column in targettable. nonprimarykeycolumns) {%>

New sqlparameter ("@ <% = column. name %> ", instance. <% = column. name %>) <% if (I <targettable. columns. count-2) {%>,<%}%>

<% I ++ ;}%>

};

Return sqlhelper. executenonquery (sqlhelper. sqlconnectionstring, commandtype. Text, executesqlstr, para );

}

/// <Summary>

/// Delete record

/// </Summary>

Public int Delete _ <% = gettablename (targettable) %> (string pkid)

{

String executesqlstr = "delete <% = targettable. name %> where <% = getprimarykeyname (targettable) % >=@ <% = getprimarykeyname (targettable) %> ";

Sqlparameter [] para = new sqlparameter [] {

New sqlparameter ("@ <% = getprimarykeyname (targettable) %>", pkid)

};

Return sqlhelper. executenonquery (sqlhelper. sqlconnectionstring, commandtype. Text, executesqlstr, para );

}

/// <Summary>

/// Update record

/// </Summary>

Public int update _ <% = gettablename (targettable) %> (<% = gettablename (targettable) %> model instance)

{

String executesqlstr = "Update <% = targettable. name %> set <% = updatecolumn (targettable) %> where <% = getprimarykeyname (targettable) % >=@ <% = getprimarykeyname (targettable) %> ";

Sqlparameter [] para = new sqlparameter [] {

<% Int J = 0; foreach (columnschema column in targettable. columns) {%>

New sqlparameter ("@ <% = column. name %> ", instance. <% = column. name %>) <% IF (j <targettable. columns. count-1) {%>,<%}%>

<% J ++ ;}%>

};

Return sqlhelper. executenonquery (sqlhelper. sqlconnectionstring, commandtype. Text, executesqlstr, para );

}

/// <Summary>

/// Get record by primary key

/// </Summary>

Public <% = gettablename (targettable) %> model get _ <% = gettablename (targettable) %> bypkid (string pkid)

{

String executesqlstr = "select <% = getcolumnname (targettable) %> from <% = gettablename (targettable) %> where <% = getprimarykeyname (targettable) % >=@ <% = getprimarykeyname (targettable) %> ";

Sqlparameter [] para = new sqlparameter [] {

New sqlparameter ("@ <% = getprimarykeyname (targettable) %>", pkid)

};

<% = Gettablename (targettable) %> model modelinstance = new <% = gettablename (targettable) %> model ();

Using (sqldatareader SDR = sqlhelper. executereader (sqlhelper. sqlconnectionstring, commandtype. Text, executesqlstr, para ))

{

If (SDR. Read ())

{

Modelinstance = evaluatemodel (SDR );

}

}

Return modelinstance;

}