<%--
Name:jiedamu
Author:zhaojie
Description:?????
--%>
<%@ CodeTemplate Language="C#" TargetLanguage="Text" Src="" Inherits="" Debug="False" Description="Template description here." %>
<%@ Property Name="SampleStringProperty" Type="System.String" Default="SomeValue" Optional="True" Category="Strings" Description="This is a sample string property." %>
<%@ Property Name="SampleBooleanProperty" Type="System.Boolean" Default="True" Optional="False" Category="Booleans" Description="This is a sample boolean property." %>
<%@ Assembly Name="System.Data" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Property Name="nameSpace" Type="String" Default="Eatery" Optional="False" Category="" Description="" OnChanged="" Editor="" EditorBase="" Serializer="" %>
<%@ Property Name="index" Type="System.int32" Default="0" Optional="False" Category="" Description="" OnChanged="" Editor="" EditorBase="" Serializer="" %>
<%@ Property Name="db" Type="SchemaExplorer.DatabaseSchema" Default="" Optional="False" Category="Context" Description="???" OnChanged="" Editor="" EditorBase="" Serializer="" %>
<%@ Import Namespace="System.IO" %>
using System;
using System.Data.Common;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;
namespace <%=nameSpace %>DAL
{
public class <%=GetClassName() %>DAL
{
/// <summary>
/// ????<%=GetClassName() %>??
/// </summary>
/// <param name="<%=GetObjectName() %>">????<%=GetClassName() %>????</param>
/// <returns>??????(0--??,1--??)</returns>
public int Add(<%=GetClassName() %> <%=GetObjectName() %>)
{
string sql =
"<%=GetInsertSql() %>";
SqlParameter[] pars = new SqlParameter[<%=db.Tables[index].Columns.Count - 1 %>]
{
<%
for(int i = 1;i < db.Tables[index].Columns.Count ;i++)
{
%>
<%
if(i == db.Tables[index].Columns.Count - 1)
{
%>
new SqlParameter("@<%=StringUtil.ToCamelCase(db.Tables[index].Columns[i].Name) %>", <%=GetObjectName() %>.<%=StringUtil.ToPascalCase(db.Tables[index].Columns[i].Name) %>)
<%
}
else
{
%>
new SqlParameter("@<%=StringUtil.ToCamelCase(db.Tables[index].Columns[i].Name) %>", <%=GetObjectName() %>.<%=StringUtil.ToPascalCase(db.Tables[index].Columns[i].Name) %>),
<%
}
%>
<%
}
%>
};
return DBHelper.ExecuteNonQuery(sql, pars);
}
/// <summary>
/// ????<%=GetClassName() %>??,?????<%=GetClassName() %>?????
/// </summary>
/// <param name="<%=GetObjectName() %>">????<%=GetClassName() %>????</param>
/// <returns><%=GetClassName() %>??</returns>
public int AddReturn<%=StringUtil.ToPascalCase(db.Tables[index].Columns[0].Name) %>(<%=GetClassName() %> <%=GetObjectName() %>)
{
string sql =
"<%=GetInsertSql() %> select @@identity as <%=db.Tables[index].Columns[0].Name %>";
SqlParameter[] pars = new SqlParameter[<%=db.Tables[index].Columns.Count - 1 %>]
{
<%
for(int i = 1;i < db.Tables[index].Columns.Count ;i++)
{
%>
<%
if(i == db.Tables[index].Columns.Count - 1)
{
%>
new SqlParameter("@<%=StringUtil.ToCamelCase(db.Tables[index].Columns[i].Name) %>", <%=GetObjectName() %>.<%=StringUtil.ToPascalCase(db.Tables[index].Columns[i].Name) %>)
<%
}
else
{
%>
new SqlParameter("@<%=StringUtil.ToCamelCase(db.Tables[index].Columns[i].Name) %>", <%=GetObjectName() %>.<%=StringUtil.ToPascalCase(db.Tables[index].Columns[i].Name) %>),
<%
}
%>
<%
}
%>
};
return int.Parse(DBHelper.ExecuteScalar(sql, pars).ToString());
}
/// <summary>
/// ????<%=GetClassName() %>??
/// </summary>
/// <param name="<%=GetObjectName() %>">????<%=GetClassName() %>????</param>
/// <returns>??????(0--??,1--??)</returns>
public int Update(<%=GetClassName() %> <%=GetObjectName() %>)
{
string sql =
"<%=GetUpdateSql() %>";
SqlParameter[] pars = new SqlParameter[<%=db.Tables[index].Columns.Count %>]
{
<%
for(int i = 1;i < db.Tables[index].Columns.Count ;i++)
{
%>
new SqlParameter("@<%=StringUtil.ToCamelCase(db.Tables[index].Columns[i].Name) %>", <%=GetObjectName() %>.<%=StringUtil.ToPascalCase(db.Tables[index].Columns[i].Name) %>),
<%
}
%>
new SqlParameter("@<%=StringUtil.ToCamelCase(db.Tables[index].Columns[0].Name) %>", <%=GetObjectName() %>.<%=StringUtil.ToPascalCase(db.Tables[index].Columns[0].Name) %>)
};
return DBHelper.ExecuteNonQuery(sql, pars);
}
/// <summary>
/// ????<%=GetClassName() %>??
/// </summary>
/// <param name="<%=StringUtil.ToCamelCase(GetPrimaryKey()) %>">????<%=GetClassName() %>??</param>
/// <returns>??????(0--??,1--??)</returns>
public int Delete(int <%=StringUtil.ToCamelCase(GetPrimaryKey()) %>)
{
string sql =
"delete from <%=db.Tables[index].Name %> where <%=StringUtil.ToCamelCase(GetPrimaryKey()) %> = @<%=StringUtil.ToCamelCase(GetPrimaryKey()) %>";
SqlParameter[] pars = new SqlParameter[1]
{
new SqlParameter("@<%=StringUtil.ToCamelCase(GetPrimaryKey()) %>",<%=StringUtil.ToCamelCase(GetPrimaryKey()) %>)
};
return DBHelper.ExecuteNonQuery(sql, pars);
}
/// <summary>
/// ????<%=GetClassName() %>??
/// </summary>
/// <returns>List<<%=GetClassName() %>>??</returns>
public List<<%=GetClassName() %>> GetAll()
{
List<<%=GetClassName() %>> lst = new List<<%=GetClassName() %>>();
string sql = "select * from <%=GetObjectName() %>";
SqlDataReader sdr = DBHelper.ExecuteReader(sql,null);
while (sdr.Read())
{
<%=GetClassName() %> <%=GetObjectName() %> = new <%=GetClassName() %>();
<%
foreach(ColumnSchema col in db.Tables[index].Columns)
{
%>
<%=GetObjectName() %>.<%=StringUtil.ToPascalCase(col.Name) %> = <%=GetType(col) %>;
<%
}
%>
lst.Add(<%=GetObjectName() %>);
}
return lst;
}
/// <summary>
/// ????<%=GetClassName() %>??
/// </summary>
/// <param name="<%=StringUtil.ToCamelCase(GetPrimaryKey()) %>">????<%=GetClassName() %>??</param>
/// <returns><%=GetClassName() %>??</returns>
public <%=GetClassName() %> GetSingle(int <%=StringUtil.ToCamelCase(GetPrimaryKey()) %>)
{
<%=GetClassName() %> <%=GetObjectName() %> = new <%=GetClassName() %>();
string sql =
"select * from teacher where <%=StringUtil.ToCamelCase(GetPrimaryKey()) %> = @<%=StringUtil.ToCamelCase(GetPrimaryKey()) %>";
SqlParameter[] pars = new SqlParameter[1]
{
new SqlParameter("@<%=StringUtil.ToCamelCase(GetPrimaryKey()) %>",<%=StringUtil.ToCamelCase(GetPrimaryKey()) %>)
};
SqlDataReader sdr = DBHelper.ExecuteReader(sql,pars);
if (sdr.HasRows)
{
sdr.Read();
<%
foreach(ColumnSchema col in db.Tables[index].Columns)
{
%>
<%=GetObjectName() %>.<%=StringUtil.ToPascalCase(col.Name) %> = <%=GetType(col) %>;
<%
}
%>
sdr.Close();
}
return <%=GetObjectName() %>;
}
/// <summary>
/// ???????<%=GetClassName() %>??
/// </summary>
/// <param name="conditions">???<%=GetClassName() %>?????</param>
/// <returns>List<<%=GetClassName() %>>??</returns>
public List<<%=GetClassName() %>> GetAllByConditions(string conditions)
{
List<<%=GetClassName() %>> lst = new List<<%=GetClassName() %>>();
string sql = "select * from <%=GetObjectName() %> where " + conditions;
SqlDataReader sdr = DBHelper.ExecuteReader(sql,null);
while (sdr.Read())
{
<%=GetClassName() %> <%=GetObjectName() %> = new <%=GetClassName() %>();
<%
foreach(ColumnSchema col in db.Tables[index].Columns)
{
%>
<%=GetObjectName() %>.<%=StringUtil.ToPascalCase(col.Name) %> = <%=GetType(col) %>;
<%
}
%>
lst.Add(<%=GetObjectName() %>);
}
sdr.Close();
return lst;
}
}
}
<script runat="template">
public string GetType(ColumnSchema col)
{
string name = col.Name;
switch (col.DataType)
{
case DbType.AnsiString:
return "Convert.ToString(sdr[/"" + name + "/"])";
case DbType.AnsiStringFixedLength:
return "Convert.ToString(sdr[/""+name+"/"])";
case DbType.Binary:
return "Convert.ToByte(sdr[/""+name+"/"])";
case DbType.Boolean:
return "Convert.ToBoolean(sdr[/""+name+"/"])";
case DbType.Byte:
return "Convert.ToByte(sdr[/""+name+"/"])";
case DbType.Currency:
return "Convert.ToDecimal(sdr[/""+name+"/"])";
case DbType.Date:
return "Convert.ToDateTime(sdr[/""+name+"/"])";
case DbType.DateTime:
return "Convert.ToDateTime(sdr[/""+name+"/"])";
case DbType.Decimal:
return "Convert.ToDecimal(sdr[/""+name+"/"])";;
case DbType.Double:
return "Convert.ToDouble(sdr[/""+name+"/"])";
case DbType.Int16:
return "Convert.ToInt16(sdr[/""+name+"/"])";
case DbType.Int32:
return "Convert.ToInt32(sdr[/""+name+"/"])";
case DbType.Int64:
return "Convert.ToInt64(sdr[/""+name+"/"])";
case DbType.Single:
return "Convert.ToSingle(sdr[/""+name+"/"])";
case DbType.String:
return "Convert.ToString(sdr[/""+name+"/"])";
case DbType.Time:
return "Convert.ToDateTime(sdr[/""+name+"/"])";
case DbType.UInt16:
return "Convert.ToInt16(sdr[/""+name+"/"])";
case DbType.UInt32:
return "Convert.ToInt32(sdr[/""+name+"/"])";
case DbType.UInt64:
return "Convert.ToInt64(sdr[/""+name+"/"])";
default:
return "Convert.ToString(sdr[/""+name+"/"])";
}
}
public string GetPrimaryKey()
{
return db.Tables[index].Columns[0].Name ;
}
public string GetClassName()
{
return StringUtil.ToPascalCase(db.Tables[index].Name);
}
public string GetObjectName()
{
return StringUtil.ToCamelCase(db.Tables[index].Name);
}
public string GetInsertSql()
{
string sql = "insert into " + GetObjectName() + " values(";
for(int i = 1;i < db.Tables[index].Columns.Count; i++)
{
sql += "@" + StringUtil.ToCamelCase(db.Tables[index].Columns[i].Name) + ",";
}
//????????
sql = sql.TrimEnd(',');
sql += ")";
return sql;
}
public string GetUpdateSql()
{
string sql = "update " + db.Tables[index].Name + " set ";
for(int i = 1;i < db.Tables[index].Columns.Count; i++)
{
sql += db.Tables[index].Columns[i].Name + " = @" + StringUtil.ToCamelCase(db.Tables[index].Columns[i].Name) + ",";
}
//????????
sql = sql.TrimEnd(',');
sql += " where " + db.Tables[index].Columns[0].Name + " = @" + StringUtil.ToCamelCase(db.Tables[index].Columns[0].Name);
return sql;
}
public override void Render(TextWriter writer)
{
string path1 =OutputDirectory + GetClassName() + "DAL.cs";
System.Diagnostics.Trace.WriteLine(path1);
StreamWriter fileWriter1 = new StreamWriter(path1,false);
this.Response.AddTextWriter(fileWriter1);
base.Render(writer);
fileWriter1.Close();
}
private string _outputDirectory = String.Empty;
[Editor(typeof(System.Windows.Forms.Design.FolderNameEditor), typeof(System.Drawing.Design.UITypeEditor))]
[Optional]
[Category("Output")]
[Description("The directory to output the results to.")]
public string OutputDirectory
{
get
{
// default to the directory that the template is located in
if (_outputDirectory.Length == 0) return this.CodeTemplateInfo.DirectoryName;
return _outputDirectory;
}
set
{
if (!value.EndsWith("//")) value += "//";
_outputDirectory = value;
}
}
</script>