using System;
[Serializable]
public class ${TableName}
{
#foreach($item in $Columns)
public ${item.ColumnsSharp} ${item.ColumnsName} { get; set; }
#end
}
using System;
using System.Collections.Generic;
using System.Data;
#set($keyName="")
#foreach($item in $Columns)
#if(${item.isKey})
#set($keyName="${item.ColumnsName}")
#end
#end
public class d${TableName}
{
public static void add(${TableName} obj)
{
DbParameters p = new DbParameters();
string sql = "insert into ${TableName}(#foreach($item in $Columns)#if(${item.isKey}==false)${item.ColumnsName}#if($velocityCount != $Columns.Count()),#end#end#end)values(#foreach($item in $Columns)#if(${item.isKey}==false)"+p.Add(obj.${item.ColumnsName})+"#if($velocityCount != $Columns.Count()),#end#end#end)";
Database.Execute(sql, p.GetDbParameters());
}
public static void modify(${TableName} obj)
{
DbParameters p = new DbParameters();
string sql = "update ${TableName} set #foreach($item in $Columns)#if(${item.isKey}==false)${item.ColumnsName}="+p.Add(obj.${item.ColumnsName})+"#if($velocityCount != $Columns.Count()),#end#end#end where ${keyName}='"+obj.${keyName};
Database.Execute(sql, p.GetDbParameters());
}
public static DataTable find(string wherestr, DbParameters p)
{
string sql = "select #foreach($item in $Columns)${item.ColumnsName}#if($velocityCount != $Columns.Count()),#end#end from ${TableName} where 1=1 " + wherestr;
return Database.ExecuteDataTable(sql,p.GetDbParameters());
}
public static DataTable findpage(int page, int pagerow, string wherestr, DbParameters p)
{
string sql = "";
if(page==1)
{
sql = "select top "+pagerow.ToString()+" #foreach($item in $Columns)${item.ColumnsName}#if($velocityCount != $Columns.Count()),#end#end from ${TableName} where 1=1 " + wherestr+" order by ${keyName} desc";
}
else
{
int rowMin = (page - 1) * pagerow + 1;
int rowMax = page * pagerow;
sql = "select ";
sql+="#foreach($item in $Columns)${item.ColumnsName}#if($velocityCount != $Columns.Count()),#end#end";
sql+=" from (select ROW_NUMBER() over(order by ${keyName} desc) RowOrderID,";
sql+="#foreach($item in $Columns)${item.ColumnsName}#if($velocityCount != $Columns.Count()),#end#end";
sql+=" from ${TableName} c where 1=1 ";
sql+=wherestr;
sql+=") datapages where RowOrderID>=" + rowMin.ToString() + " and RowOrderID<=" + rowMax.ToString();
}
return Database.ExecuteDataTable(sql,p.GetDbParameters());
}
public static int count(string wherestr, DbParameters p)
{
string sql = "select count(*) from ${TableName} where 1=1 " + wherestr;
DataTable dt = Database.ExecuteDataTable(sql, p.GetDbParameters());
if (dt != null && dt.Rows.Count >0 && dt.Rows[0][0] != null && dt.Rows[0][0] != DBNull.Value)
{
return Convert.ToInt32(dt.Rows[0][0]);
}
return 0;
}
}
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
public class DbParameters
{
List<DbParameter> dbParameters = new List<DbParameter>();
int Index = 0;
public void Add(string ParameterName, object Value)
{
DbParameter dbParameter = new SqlParameter();
dbParameter.ParameterName = "@" + ParameterName;
dbParameter.Value = Value ?? DBNull.Value;
dbParameters.Add(dbParameter);
}
public string Add(object Value)
{
DbParameter dbParameter = new SqlParameter();
dbParameter.ParameterName = "@p" + Index.ToString();
dbParameter.Value = null;
dbParameters.Add(dbParameter);
Index++;
return dbParameter.ParameterName;
}
public DbParameter[] GetDbParameters()
{
return dbParameters.ToArray();
}
}
public class Database
{
public static readonly string ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
public static bool Add(string tablename, DbParameters p)
{
string sql = string.Empty;
string sqlinsert = string.Empty;
string sqlvalues = string.Empty;
DbParameter[] dbParameters = p.GetDbParameters();
foreach (DbParameter parameter in dbParameters)
{
sqlinsert += parameter.ParameterName.Replace("@", string.Empty) + ",";
sqlvalues += parameter.ParameterName + ",";
}
sqlinsert = sqlinsert.Trim(',');
sqlvalues = sqlvalues.Trim(',');
sql = string.Format("insert into [{0}] ({1}) values ({2})", tablename, sqlinsert, sqlvalues);
return Execute(sql, dbParameters);
}
public static bool Modify(string tablename, DbParameters p, object value)
{
string sql = string.Empty;
string sqlupdate = string.Empty;
DbParameter[] dbParameters = p.GetDbParameters();
foreach (DbParameter parameter in dbParameters)
{
sqlupdate += string.Format("{0}=@{0},", parameter.ParameterName.Replace("@", string.Empty));
}
sqlupdate = sqlupdate.Trim(',');
sql = string.Format("UPDATE [{0}] SET {1} where id={2}", tablename, sqlupdate, p.Add(value));
return Execute(sql, dbParameters);
}
public static DataRow GetModel(string tablename, object value)
{
DbParameters p = new DbParameters();
string sql = string.Format("select * from [{0}] where id={1}", tablename, p.Add(value));
DataTable dt = ExecuteDataTable(sql, p.GetDbParameters());
if (dt != null && dt.Rows.Count > 0)
{
return dt.Rows[0];
}
return null;
}
public static bool Delete(string tablename, object value)
{
DbParameters p = new DbParameters();
string sql = string.Format("delete from [{0}] where id={1}", tablename, p.Add(value));
return Execute(sql, p.GetDbParameters());
}
public static bool Execute(string sql, params DbParameter[] dbParameters)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
try
{
connection.Open();
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddRange(dbParameters);
return command.ExecuteNonQuery() > 0;
}
}
finally
{
if (connection.State != ConnectionState.Closed)
connection.Close();
}
}
}
public static DataTable ExecuteDataTable(string sql, params DbParameter[] dbParameters)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
try
{
connection.Open();
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddRange(dbParameters);
SqlDataAdapter dataAdapter = new SqlDataAdapter();
dataAdapter.SelectCommand = command;
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
return dataTable;
}
}
finally
{
if (connection.State != ConnectionState.Closed)
connection.Close();
}
}
}
}