/// <summary>
/// 获取表的列信息
/// </summary>
/// <param name="tebleName">要查询的表</param>
/// <returns>返回列信息集合</returns>
private static List <ColumnContent> GetColumns(string tebleName)
{
string sql = string.Format(@"SELECT CASE WHEN EXISTS
(SELECT 1 FROM sysobjects
WHERE xtype = 'PK' AND parent_obj = a.id AND name IN
(SELECT name FROM sysindexes WHERE indid IN
(SELECT indid FROM sysindexkeys
WHERE id = a.id AND colid = a.colid))) THEN '1' ELSE '0' END AS 'key',
CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1
THEN '1' ELSE '0' END AS 'identity', a.name AS ColName, c.name AS TypeName, a.length AS 'byte',
COLUMNPROPERTY(a.id, a.name,
'PRECISION') AS 'length', a.xscale, a.isnullable, ISNULL(e.text, '') AS 'default', ISNULL(p.value, '') AS 'comment'
FROM sys.syscolumns AS a INNER JOIN
sys.sysobjects AS b ON a.id = b.id INNER JOIN
sys.systypes AS c ON a.xtype = c.xtype LEFT OUTER JOIN
sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN
sys.extended_properties AS p ON a.id = p.major_id AND a.colid = p.minor_id
WHERE (b.name = @Table) AND (c.status <> '1')
");
SqlParameter[] par = new SqlParameter[] {
new SqlParameter("Table", tebleName)
};
//获取表的列信息
SqlDataReader red = SQLHelper.ExecuteReader(GetConn(), CommandType.Text, sql, par);
List <ColumnContent> list = new List <ColumnContent>();
while (red.Read())
{
ColumnContent cc = new ColumnContent();
cc.Key = Convert.ToInt32(red["Key"]) == 1;
cc.Identity = Convert.ToInt32(red["Identity"]) == 1;
cc.ColName = (string)red["ColName"];
cc.TypeName = ChangeTypeName((string)red["TypeName"]);
cc.Byte = Convert.ToInt32(red["Byte"]);
cc.Length = (int)red["Length"];
cc.Xscale = Convert.ToInt32(red["Xscale"]);
cc.Isnullable = Convert.ToInt32(red["Isnullable"]) == 1;
cc.Default = (string)red["Default"];
cc.Comment = (string)red["Comment"];
list.Add(cc);
}
red.Close();
return(list);
}
/// <summary>
/// 生成DAL项目
/// </summary>
///<param name="savePath">存放路径</param>
/// <param name="nameSpace">命名空间</param>
/// <param name="sourcePath">模版路径</param>
private static void CreateDAL(string savePath, string nameSpace, string sourcePath)
{
string path = savePath + "\\" + nameSpace + "\\DAL";
//写AssemblyInfo文件
StreamReader sr = new StreamReader(sourcePath + "\\AssemblyInfo.cs", Encoding.UTF8);
string str = sr.ReadToEnd();
str = str.Replace("{project name}", "DAL")
.Replace("{guid}", Guid.NewGuid().ToString().ToLower())
.Replace("{year}", DateTime.Today.Year.ToString());
sr.Close();
StreamWriter sw = new StreamWriter(path + "\\Properties\\AssemblyInfo.cs", false, Encoding.UTF8);
sw.Write(str);
sw.Close();
//写Service文件
string[] tables = GetTableNames();
foreach (string table in tables)
{
List <ColumnContent> list = GetColumns(table.ToDelKh());
string insertFieldList = string.Empty;
string insertValueList = string.Empty;
string insertParamList = string.Empty;
StringBuilder selectConditionList = new StringBuilder();
StringBuilder selectSetProperty = new StringBuilder();
StringBuilder updateFields = new StringBuilder();
for (int i = 0; i < list.Count; i++)
{
ColumnContent ct = list[i];
if (!ct.Identity)
{
insertFieldList += string.Format("{0},", ct.ColName);
insertValueList += string.Format("@{0},", ct.ColName.ToPascal().ToUpper());
insertParamList += string.Format(" new SqlParameter(\"@{0}\",{1}.{2} == null ? Convert.DBNull : {1}.{2}),\r\n", ct.ColName.ToPascal().ToUpper(), table.ToCamel(), ct.ColName.ToPascal());
updateFields.AppendFormat(
" if ({0}.{1} != null)\r\n" +
" {{\r\n" +
" fields += \"{2}=@Update{3},\";\r\n" +
" paraList.Add(new SqlParameter(\"@Update{3}\", {0}.{1}));\r\n" +
" }}{4}",
table.ToCamel(), ct.ColName.ToPascal(), ct.ColName, ct.ColName.ToPascal().ToUpper(), (i < list.Count - 1 ? "\r\n" : ""));
}
selectConditionList.AppendFormat(
" if ({0}.{1} != null)\r\n" +
" {{\r\n" +
" sql += \" and {2}=@{3}\";\r\n" +
" paraList.Add(new SqlParameter(\"@{3}\", {0}.{1}));\r\n" +
" }}{4}",
table.ToCamel(), ct.ColName.ToPascal(), ct.ColName, ct.ColName.ToPascal().ToUpper(), (i < list.Count - 1 ? "\r\n" : ""));
selectSetProperty.AppendFormat(
" obj.{0} = Convert.IsDBNull(reader[\"{1}\"]) ? null : ({2})reader[\"{1}\"];{3}",
ct.ColName.ToPascal(), ct.ColName.Trim(new char[] { '[', ']' }), ct.TypeName, (i < list.Count - 1 ? "\r\n" : ""));
}
insertFieldList = insertFieldList.Substring(0, insertFieldList.Length - 1);
insertValueList = insertValueList.Substring(0, insertValueList.Length - 1);
insertParamList = insertParamList.Substring(0, insertParamList.Length - 3);
sr = new StreamReader(sourcePath + "\\Service.cs", Encoding.UTF8);
str = sr.ReadToEnd();
sr.Close();
str = str.Replace("{namespace}", nameSpace)
.Replace("{model pascal}", table.ToPascal())
.Replace("{model camel}", table.ToCamel())
.Replace("{table name}", table)
.Replace("{field list}", insertFieldList)
.Replace("{value list}", insertValueList)
.Replace("{insert param list}", insertParamList)
.Replace("{updateFields}", updateFields.ToString())
.Replace("{condition list}", selectConditionList.ToString())
.Replace("{set property}", selectSetProperty.ToString())
;
sw = new StreamWriter(path + "\\" + table.ToPascal() + "Service.cs", false, Encoding.UTF8);
sw.Write(str);
sw.Close();
}
sqlserverdalGuid = Guid.NewGuid().ToString().ToUpper();
sr = new StreamReader(sourcePath + "\\Project.csproj", Encoding.UTF8);
str = sr.ReadToEnd();
sr.Close();
StringBuilder includeFiles = new StringBuilder();
foreach (string table in tables)
{
includeFiles.Append(" <Compile Include=\"" + table.ToPascal() + "Service.cs\" />\r\n");
}
string refProj =
" <ProjectReference Include=\"..\\Model\\Model.csproj\">\r\n" +
" <Project>{" + modelGuid + "}</Project>\r\n" +
" <Name>Model</Name>\r\n" +
" </ProjectReference>\r\n" +
" <ProjectReference Include=\"..\\DBUtility\\DBUtility.csproj\">\r\n" +
" <Project>{" + dbutilityGuid + "}</Project>\r\n" +
" <Name>DBUtility</Name>\r\n" +
" </ProjectReference>\r\n";
str = str.Replace("{guid}", sqlserverdalGuid)
.Replace("{namespace}", nameSpace)
.Replace("{project name}", "DAL")
.Replace("{reference assembly}", "")
.Replace("{reference project}", refProj)
.Replace("{include files}", includeFiles.ToString());
sw = new StreamWriter(path + "\\DAL.csproj", false, Encoding.UTF8);
sw.Write(str);
sw.Close();
}
/// <summary>
/// 生成Model项目
/// </summary>
///<param name="savePath">存放路径</param>
/// <param name="nameSpace">命名空间</param>
/// <param name="sourcePath">模版路径</param>
private static void CreateModel(string savePath, string nameSpace, string sourcePath)
{
string path = savePath + "\\" + nameSpace + "\\Model";
//写AssemblyInfo文件
StreamReader sr = new StreamReader(sourcePath + "\\AssemblyInfo.cs", Encoding.UTF8);
string str = sr.ReadToEnd();
str = str.Replace("{project name}", "Model")
.Replace("{guid}", Guid.NewGuid().ToString().ToLower())
.Replace("{year}", DateTime.Today.Year.ToString());
sr.Close();
StreamWriter sw = new StreamWriter(path + "\\Properties\\AssemblyInfo.cs", false, Encoding.UTF8);
sw.Write(str);
sw.Close();
//实体类
string[] tableNames = GetTableNames();
foreach (string tableName in tableNames)
{
List <ColumnContent> list = GetColumns(tableName.ToDelKh());
StringBuilder sb = new StringBuilder();
StringBuilder Par = new StringBuilder();
StringBuilder Set = new StringBuilder();
for (int i = 0; i < list.Count; i++)
{
ColumnContent ct = list[i];
//带参构造参数
Par.Append(ct.TypeName + " " + ct.ColName.ToPascal() + (i < list.Count - 1 ? ", " : ""));
//带参构造赋值
Set.Append(" this." + ct.ColName.ToPascal() + " = " + ct.ColName.ToPascal() + ";" + (i < list.Count - 1 ? "\r\n" : ""));
//自动属性
sb.Append(" /// <summary>\r\n");
sb.Append(" /// " + ct.Comment.Replace("\r", "").Replace("\n", "").Trim() + "\r\n");
sb.Append(" /// </summary>\r\n");
sb.Append(" public " + ct.TypeName + " " + ct.ColName.ToPascal() + " { get; set; }" + (i < list.Count - 1 ? "\r\n\r\n" : ""));
}
sr = new StreamReader(sourcePath + "\\Model.cs", Encoding.UTF8);
str = sr.ReadToEnd();
sr.Close();
str = str.Replace("{namespace}", nameSpace)
.Replace("{classname}", tableName.ToPascal())
.Replace("{properties}", sb.ToString())
.Replace("{propertiesPar}", Par.ToString())
.Replace("{propertiesSet}", Set.ToString());
sw = new StreamWriter(path + "\\" + tableName.ToPascal() + ".cs", false, Encoding.UTF8);
sw.Write(str);
sw.Close();
}
//写csproj项目文件
modelGuid = Guid.NewGuid().ToString().ToUpper();
sr = new StreamReader(sourcePath + "\\Project.csproj", Encoding.UTF8);
str = sr.ReadToEnd();
sr.Close();
StringBuilder includeFiles = new StringBuilder();
foreach (string table in tableNames)
{
includeFiles.Append(" <Compile Include=\"" + table.ToPascal() + ".cs\" />\r\n");
}
str = str.Replace("{guid}", modelGuid)
.Replace("{namespace}", nameSpace)
.Replace("{project name}", "Model")
.Replace("{reference assembly}", "")
.Replace("{reference project}", "")
.Replace("{include files}", includeFiles.ToString());
sw = new StreamWriter(path + "\\Model.csproj", false, Encoding.UTF8);
sw.Write(str);
sw.Close();
}