自动生成数据库表的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 IsIndex
FROM    [INFORMATION_SCHEMA].[COLUMNS] Cols
ORDER 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