自动生成数据库表的insert，update，delete存储过程工具

1. 工具下载

2. 代码下载及代码分析

---

1. 工具下载

 

如何生成数据库中某张表的插入，删除，更新的存储过程，可以使用这个工具，这里可以下载：下载地址，界面如下：

 

 

 

---

 

2. 代码下载及代码分析

 

2.1 代码下载

 

2.2 代码分析

 

在.net中代码的自动生成简单的哦可以通过string的拼接实现，另外的可以考虑使用codedom来实现代码自动生成。这个示例中使用的是“string拼接”实现的：

 

public static string CreateInsertSP(string spName, string TableName, DataRow[] Columns) { string SQL = string.Empty; SQL = String.Format(Resources.DropProcedure, spName); SQL += "/r/n/r/n"; SQL += "-- =========================================================================================="; SQL += "/r/n-- Entity Name:/t" + spName; string AuthorName = Session.LoadFromSession("AuthorName").ToString(); if (AuthorName != string.Empty) { SQL += "/r/n-- Author:/t" + AuthorName; } SQL += "/r/n-- Create date:/t" + DateTime.Now.ToString(); SQL += "/r/n-- Description:/tThis stored procedure is intended for inserting values to " + TableName + " table"; SQL += "/r/n-- ==========================================================================================/r/n"; #region "Header Definition" SQL += "Create Procedure " + spName + "/r/n"; #endregion #region "Parameter Definition" bool firstParam = true; foreach (DataRow row in Columns) { if (int.Parse(row["IsIdentity"].ToString()) == 0) { if (firstParam == true) { firstParam = false; SQL += "/t"; } else { SQL += ",/r/n/t"; } SQL += "@" + row["COLUMN_NAME"] + " "; if (row["DATA_TYPE"].ToString().ToLower().Contains("char")) { string Length = (row["CHARACTER_MAXIMUM_LENGTH"].ToString().Equals("-1") ? "MAX" : row["CHARACTER_MAXIMUM_LENGTH"].ToString()); SQL += row["DATA_TYPE"].ToString() + "(" + Length + ")"; } else if (row["DATA_TYPE"].ToString().ToLower().Contains("numeric")) { SQL += string.Format("numeric({0:G},{1:G})", row["NUMERIC_PRECISION"].ToString(), row["NUMERIC_SCALE"].ToString()); } else { SQL += row["DATA_TYPE"].ToString(); } bool NullParamDefaultValues = bool.Parse(Session.LoadFromSession("NullParamDefaultValues").ToString()); if (row["IS_NULLABLE"].ToString().ToLower() == "yes" && NullParamDefaultValues == true) { SQL += " = NULL"; } } } #endregion #region "Insert Command / Header Definition" SQL += "/r/nAs/r/nBegin/r/n"; SQL += "/tInsert Into " + TableName + "/r/n/t/t("; #endregion #region "Insert Command / Target Columns Definition" firstParam = true; foreach (DataRow row in Columns) { if (int.Parse(row["IsIdentity"].ToString()) == 0) { if (firstParam == true) { firstParam = false; } else { SQL += ","; } SQL += QualifyFieldName(row["COLUMN_NAME"].ToString()); } } SQL += ")/r/n/tValues/r/n/t/t("; #endregion #region "Insert Command / Supplying Values Definition" firstParam = true; foreach (DataRow row in Columns) { if (int.Parse(row["IsIdentity"].ToString()) == 0) { if (firstParam == true) { firstParam = false; } else { SQL += ","; } SQL += "@" + row["COLUMN_NAME"].ToString(); } } SQL += ")/r/n"; #endregion #region "Return Identity , if any identity columns found" bool identityExists = false; foreach (DataRow row in Columns) { if (int.Parse(row["IsIdentity"].ToString()) != 0) { identityExists = true; break; } } if (identityExists == true) { SQL += "/r/n/tDeclare @ReferenceID int"; SQL += "/r/n/tSelect @ReferenceID = @@IDENTITY/r/n"; } #endregion #region "Primary Key Column Detection" string pkColumn = string.Empty; foreach (DataRow row in Columns) { if (int.Parse(row["IsIndex"].ToString()) != 0) { pkColumn = row["COLUMN_NAME"].ToString(); break; } } #endregion if (identityExists == true) { SQL += "/r/n/tReturn @ReferenceID/r/n"; } SQL += "/r/nEnd/r/n/r/nGO/r/n"; return SQL; }

 

那么现在还存在下面的几个问题：如何得到数据库中某张表的信息，列名，类型等？关键代码：

string x = string.Format(Resources.strTablesAndColumns, dbName); DataSet dsTablesAndColumns = dbo.RunQuery(x, "TablesAndColumns"); DataRow[] rows = dsTablesAndColumns.Tables[0].Select("Table_Name = '" + tableName + "'");

 

调试过程中可以看到x在运行时得到的是如下的字符串：

 

 

USE EntLibQuickStarts;SELECT Cols.TABLE_NAME, Cols.COLUMN_NAME, Cols.ORDINAL_POSITION, Cols.DATA_TYPE,Cols.NUMERIC_PRECISION,Cols.NUMERIC_SCALE, Cols.IS_NULLABLE, Cols.CHARACTER_MAXIMUM_LENGTH, COLUMNPROPERTY(object_id(Cols.TABLE_NAME), Cols.COLUMN_NAME, 'IsIdentity') AS IsIdentity, ( SELECT COUNT(KCU.COLUMN_NAME) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON KCU.TABLE_NAME = TC.TABLE_NAME AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY' WHERE KCU.TABLE_NAME = Cols.TABLE_NAME AND KCU.COLUMN_NAME = Cols.COLUMN_NAME ) AS IsIndexFROM [INFORMATION_SCHEMA].[COLUMNS] ColsORDER BY Cols.TABLE_NAME, Cols.ORDINAL_POSITION

 

查看上面生成的sql语句，发现[INFORMATION_SCHEMA].[COLUMNS] Cols，那么这是什么？展开sql management studio中某个数据库的views下面的system view：

 

 

原来在sql server数据库中存在一些视图，能够存取该数据库中表的相关信息，具体可以查看这里：http://www.mssqltips.com/tutorial.asp?tutorial=179

 

 

---