开源项目DataTierGenerator学习（一）

    此文章以DataTierGenerator的最早版本1.1.2为基础。现在DataTierGenerator发展了很多版本，以前的版本出到4.0版，后来又出了DataTierGenerator-EntLib版。能够根据数据表自动生成存储过程和相应的数据操作类。

在生成实体操作类中4.1版做了很大的改进。利用了CSharp3.0的<List>特性。不错不错啊，

并且不单单是生成文件。而是生成一个项目。

，做这种东西，需要对系统的存储过程和系统表非常熟悉，

并对输入输出流掌握。

 

1、

在主窗体的构造方法中使用了注册表调用上次的输入记录。

 

using (RegistryKey registryKey = Registry.CurrentUser.CreateSubKey(@"Software/SourceForge/CSharpDataTier"))

{

        serverTextBox.Text = (string) registryKey.GetValue("Server", String.Empty);

        databaseTextBox.Text = (string) registryKey.GetValue("Database", String.Empty);

        if (((int) registryKey.GetValue("AuthenticationType", 1)) == 1)

        {

          windowsAuthenticationRadioButton.Checked = true;

        }

        else

        {

          sqlServerAuthenticationRadioButton.Checked = true;

        }

        loginNameTextBox.Text = (string) registryKey.GetValue("Login", String.Empty);

        grantUserTextBox.Text = (string) registryKey.GetValue("GrantUser", String.Empty);

        storedProcedurePrefixTextBox.Text = (string) registryKey.GetValue("StoredProcedurePrefix", String.Empty);

        if (((int) registryKey.GetValue("CreateMultipleFiles", 0)) == 1)

        {

            multipleFilesCheckBox.Checked = true;

                                }

            namespaceTextBox.Text = (string) registryKey.GetValue("Namespace", String.Empty);

            daoSuffixTextBox.Text = (string) registryKey.GetValue("DaoSuffix", String.Empty);

}

 

 

注意：操作注册表的命名空间 using Microsoft.Win32;

 

 

2、生成连接字符串：

 

if (windowsAuthenticationRadioButton.Checked)

{

      connectionString = "Server=" + serverTextBox.Text + "; Database=" + databaseTextBox.Text + "; Integrated Security=sspi;";

}

else

{

  connectionString = "Server=" + serverTextBox.Text + "; Database=" + databaseTextBox.Text + "; User ID=" + loginNameTextBox.Text + "; Password=" + passwordTextBox.Text + ";";

}

 

 

3、在定义一个字符变量为空时这样写：

string outputDirectory = String.Empty;

最好不要写成string outputDirectory;  一个是习惯上的问题，一个是严谨和安全上考虑。

 

 

4、采用事件弹出选择文件夹窗体，而不是在构造中先实例化

 

using (FolderBrowserDialog dialog = new FolderBrowserDialog())

{

        dialog.Description = "Select an output directory";

        dialog.ShowNewFolderButton = true;

        if (dialog.ShowDialog(this) == DialogResult.OK)

        {

         outputDirectory = dialog.SelectedPath;

        }

        else

        {

           return;

        }

}

 

 

5、写数据到注册表

                                using (RegistryKey registryKey = Registry.CurrentUser.CreateSubKey(@"Software/SourceForge/CSharpDataTier")) {

                                        registryKey.SetValue("Server", serverTextBox.Text, RegistryValueKind.String);

                                        registryKey.SetValue("Database", databaseTextBox.Text, RegistryValueKind.String);

                                        if (windowsAuthenticationRadioButton.Checked) {

                                                registryKey.SetValue("AuthenticationType", 1, RegistryValueKind.DWord);

                                        } else {

                                                registryKey.SetValue("AuthenticationType", 2, RegistryValueKind.DWord);

                                        }

                                        registryKey.SetValue("Login", loginNameTextBox.Text, RegistryValueKind.String);

                                        registryKey.SetValue("GrantUser", grantUserTextBox.Text, RegistryValueKind.String);

                                        registryKey.SetValue("StoredProcedurePrefix", storedProcedurePrefixTextBox.Text, RegistryValueKind.String);

                                        if (multipleFilesCheckBox.Checked) {

                                                registryKey.SetValue("CreateMultipleFiles", 1, RegistryValueKind.DWord);

                                        } else {

                                                registryKey.SetValue("CreateMultipleFiles", 0, RegistryValueKind.DWord);

                                        }

                                        registryKey.SetValue("Namespace", namespaceTextBox.Text, RegistryValueKind.String);

                                        registryKey.SetValue("DaoSuffix", daoSuffixTextBox.Text, RegistryValueKind.String);

                                }

 

6、遍历连接数据库中的的所有表

 

                        using (SqlConnection connection = new SqlConnection(connectionString)) {

                                databaseName = Utility.FormatPascal(connection.Database);

                                sqlPath = Path.Combine(outputDirectory, "SQL");

                                csPath = Path.Combine(outputDirectory, "CS");

 

                                connection.Open();

                               

                                // Get a list of the entities in the database

                                DataTable dataTable = new DataTable();

                                SqlDataAdapter dataAdapter = new SqlDataAdapter(Utility.GetTableQuery(connection.Database), connection);

                                dataAdapter.Fill(dataTable);

                                

                                // Process each table

                                foreach (DataRow dataRow in dataTable.Rows) {

                                        Table table = new Table();

                                        table.Name = (string) dataRow["TABLE_NAME"];

                                        QueryTable(connection, table);

                                        tableList.Add(table);

                                }

                        }

 

7、生成各表的7类存储过程和类文件

 

                       if (tableList.Count > 0) {

                                // Create the necessary directories

                                Utility.CreateSubDirectory(sqlPath, true);

                                Utility.CreateSubDirectory(csPath, true);

                               

                                // Create the necessary database logins

                                SqlGenerator.CreateUserQueries(databaseName, grantLoginName, sqlPath, createMultipleFiles);

                               

                                // Create the CRUD stored procedures and data access code for each table

                                foreach (Table table in tableList) {

                                        SqlGenerator.CreateInsertStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);

                                        SqlGenerator.CreateUpdateStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);

                                        SqlGenerator.CreateDeleteStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);

                                        SqlGenerator.CreateDeleteAllByStoredProcedures(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);

                                        SqlGenerator.CreateSelectStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);

                                        SqlGenerator.CreateSelectAllStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);

                                        SqlGenerator.CreateSelectAllByStoredProcedures(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);

                                       

                                        CsGenerator.CreateEntityClass(table, targetNamespace, storedProcedurePrefix, csPath);

                                        

                                        count++;

                                        TableCounted(null, new CountEventArgs(count));

                                }

                               

                                CsGenerator.CreateSharpCore(csPath);

                                CsGenerator.CreateAssemblyInfo(csPath, databaseName, databaseName);

                                CsGenerator.CreateProjectFile(csPath, databaseName, tableList);

                        }

 

8、遍历表的列信息，利用系统存储过程得到所有用户表的信息，然后遍历内容。

利用Master表的视图INFORMATION_SCHEMA.SCHEMATA。遍历出整个连接中的数据库信息。

 

                        foreach (DataRow columnRow in dataTable.Rows) {

                                Column column = new Column();

                                column.Name = columnRow["COLUMN_NAME"].ToString();

                                column.Type = columnRow["DATA_TYPE"].ToString();

                                column.Precision = columnRow["NUMERIC_PRECISION"].ToString();

                                column.Scale = columnRow["NUMERIC_SCALE"].ToString();

                               

                                // Determine the column's length

                                if (columnRow["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value) {

                                        column.Length = columnRow["CHARACTER_MAXIMUM_LENGTH"].ToString();

                                } else {

                                        column.Length = columnRow["COLUMN_LENGTH"].ToString();

                                }

 

                                // Is the column a RowGuidCol column?

                                if (columnRow["IS_ROWGUIDCOL"].ToString() == "1") {

                                        column.IsRowGuidCol = true;

                                }

 

                                // Is the column an Identity column?

                                if (columnRow["IS_IDENTITY"].ToString() == "1") {

                                        column.IsIdentity = true;

                                }

 

                                // Is columnRow column a computed column?

                                if (columnRow["IS_COMPUTED"].ToString() == "1") {

                                        column.IsComputed = true;

                                }

                               

                                table.Columns.Add(column);

                        }

 

 

 

9、获取主键信息，利用系统存储过程 sp_pkeys

                        foreach (DataRow primaryKeyRow in primaryKeyTable.Rows) {

                                string primaryKeyName = primaryKeyRow["COLUMN_NAME"].ToString();

                               

                                foreach (Column column in table.Columns) {

                                        if (column.Name == primaryKeyName) {

                                                table.PrimaryKeys.Add(column);

                                                break;

                                        }

                                }

                        }

 

10、获取外键列表信息，利用系统存储过程 sp_fkeys

                        foreach (DataRow foreignKeyRow in foreignKeyTable.Rows) {

                                string name = foreignKeyRow["FK_NAME"].ToString();

                                string columnName = foreignKeyRow["FKCOLUMN_NAME"].ToString();

                               

                                if (table.ForeignKeys.ContainsKey(name) == false) {

                                        table.ForeignKeys.Add(name, new ArrayList());

                                }

                               

                                ArrayList foreignKeys = (ArrayList) table.ForeignKeys[name];

                               

                                foreach (Column column in table.Columns) {

                                        if (column.Name == columnName) {

                                                foreignKeys.Add(column);

                                                break;

                                        }

                                }

                        }

 

11、深度解析Utility.cs类文件

在程序中全部使用了程序级的访问级别internal，而没有采用public,增大了安全系数

 

A.建立文件夹，存在则不执行

 

                internal static void CreateSubDirectory(string name) {

                        if (Directory.Exists(name) == false) {

                                Directory.CreateDirectory(name);

                        }

                }

               

 

 

B、建立文件夹，存在则删除重新建立

                internal static void CreateSubDirectory(string name, bool deleteIfExists) {

                        if (Directory.Exists(name)) {

                                Directory.Delete(name, true);

                        }

 

                        Directory.CreateDirectory(name);

                }

 

C、获取项目资源列表

                internal static Stream GetResourceAsStream(string name) {

                        return Assembly.GetExecutingAssembly().GetManifestResourceStream(name);

                }

 

D、把流读取到字符串

                internal static string GetResource(string name) {

                        using (StreamReader streamReader = new StreamReader(GetResourceAsStream(name))) {

                                return streamReader.ReadToEnd();

                        }

                }

 

E、把流读取到字符串，并用新的替换原来的流

                internal static string GetResource(string name, string oldValue, string newValue) {

                        string returnValue = GetResource(name);

                        return returnValue.Replace(oldValue, newValue);

                }

 

F、把一个字符串读取为流，写入到文件。

                internal static void WriteResourceToFile(string resourceName, string fileName) {

                        using (FileStream fileStream = new FileStream(fileName, FileMode.Create)) {

                                using (Stream stream = Utility.GetResourceAsStream(resourceName)) {

                                        while (true) {

                                                int intValue = stream.ReadByte();

                                                if (intValue >= 0) {

                                                        byte byteValue = (byte) intValue;

                                                        fileStream.WriteByte(byteValue);

                                                } else {

                                                        break;

                                                }

                                        }

                                }

                        }

                }

 

G、查询出Master库中的所有表的相关数据，而不是视图。利用INFORMATION_SCHEMA.TABLES 视图

                internal static string GetTableQuery(string databaseName) {

                        return GetResource("DataTierGenerator.TableQuery.sql", "#DatabaseName#", databaseName);

                }

 

H、查询出Master库中的所有表的字段信息，利用INFORMATION_SCHEMA.COLUMNS视图

                internal static string GetColumnQuery(string tableName) {

                        return GetResource("DataTierGenerator.ColumnQuery.sql", "#TableName#", tableName);

                }

 

I、查询出所有用户列表，利用系统存储过程sysusers，

                internal static string GetUserQueries(string databaseName, string grantLoginName) {

                        string returnValue = GetResource("DataTierGenerator.User.sql");

                        returnValue = returnValue.Replace("#DatabaseName#", databaseName);

                        returnValue = returnValue.Replace("#UserName#", grantLoginName);

                        return returnValue;

                }

 

J、获取外键列表

                internal static DataTable GetForeignKeyList(SqlConnection connection, string tableName) {

                        SqlParameter parameter;

                       

                        using (SqlCommand command = new SqlCommand("sp_fkeys", connection)) {

                                command.CommandType = CommandType.StoredProcedure;

                               

                                parameter = new SqlParameter("@pktable_name", SqlDbType.NVarChar, 128, ParameterDirection.Input, true, 0, 0, "pktable_name", DataRowVersion.Current, DBNull.Value);

                                command.Parameters.Add(parameter);

                                parameter = new SqlParameter("@pktable_owner", SqlDbType.NVarChar, 128, ParameterDirection.Input, true, 0, 0, "pktable_owner", DataRowVersion.Current, DBNull.Value);

                                command.Parameters.Add(parameter);

                                parameter = new SqlParameter("@pktable_qualifier", SqlDbType.NVarChar, 128, ParameterDirection.Input, true, 0, 0, "pktable_qualifier", DataRowVersion.Current, DBNull.Value);

                                command.Parameters.Add(parameter);

                                parameter = new SqlParameter("@fktable_name", SqlDbType.NVarChar, 128, ParameterDirection.Input, true, 0, 0, "fktable_name", DataRowVersion.Current, tableName);

                                command.Parameters.Add(parameter);

                                parameter = new SqlParameter("@fktable_owner", SqlDbType.NVarChar, 128, ParameterDirection.Input, true, 0, 0, "fktable_owner", DataRowVersion.Current, DBNull.Value);

                                command.Parameters.Add(parameter);

                                parameter = new SqlParameter("@fktable_qualifier", SqlDbType.NVarChar, 128, ParameterDirection.Input, true, 0, 0, "fktable_qualifier", DataRowVersion.Current, DBNull.Value);

                                command.Parameters.Add(parameter);

                               

                                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);

                                DataTable dataTable = new DataTable();

                                dataAdapter.Fill(dataTable);

                               

                                return dataTable;

                        }

                }

 

K、获取主键列表

                internal static DataTable GetPrimaryKeyList(SqlConnection connection, string tableName) {

                        SqlParameter parameter;

                       

                        using (SqlCommand command = new SqlCommand("sp_pkeys", connection)) {

                                command.CommandType = CommandType.StoredProcedure;

                               

                                parameter = new SqlParameter("@table_name", SqlDbType.NVarChar, 128, ParameterDirection.Input, false, 0, 0, "table_name", DataRowVersion.Current, tableName);

                                command.Parameters.Add(parameter);

                                parameter = new SqlParameter("@table_owner", SqlDbType.NVarChar, 128, ParameterDirection.Input, true, 0, 0, "table_owner", DataRowVersion.Current, DBNull.Value);

                                command.Parameters.Add(parameter);

                                parameter = new SqlParameter("@table_qualifier", SqlDbType.NVarChar, 128, ParameterDirection.Input, true, 0, 0, "table_qualifier", DataRowVersion.Current, DBNull.Value);

                                command.Parameters.Add(parameter);

                               

                                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);

                                DataTable dataTable = new DataTable();

                                dataAdapter.Fill(dataTable);

                               

                                return dataTable;

                        }

                }

 

L、设置参数字符串，把所有的数据类型都进行Case筛选。共有26种存储过程 ，其中decimal和numeric须做判断。

                internal static string CreateParameterString(Column column, bool checkForOutputParameter) {

                        string parameter;

 

                        switch (column.Type.ToLower()) {

                                case "binary":

                                        parameter = "@" + column.Name + " " + column.Type + "(" + column.Length + ")";

                                        break;

                                case "bigint":

                                        parameter = "@" + column.Name + " " + column.Type;

                                        break;

                                case "bit":

                                        parameter = "@" + column.Name + " " + column.Type;

                                        break;

                                case "char":

                                        parameter = "@" + column.Name + " " + column.Type + "(" + column.Length + ")";

                                        break;

                                case "datetime":

                                        parameter = "@" + column.Name + " " + column.Type;

                                        break;

                                case "decimal":

                                        if (column.Scale.Length == 0)

                                                parameter = "@" + column.Name + " " + column.Type + "(" + column.Precision + ")";

                                        else

                                                parameter = "@" + column.Name + " " + column.Type + "(" + column.Precision + ", "+ column.Scale + ")";

                                        break;

                                case "float":

                                        parameter = "@" + column.Name + " " + column.Type + "(" + column.Precision + ")";

                                        break;

                                case "image":

                                        parameter = "@" + column.Name + " " + column.Type;

                                        break;

                                case "int":

                                        parameter = "@" + column.Name + " " + column.Type;

                                        break;

                                case "money":

                                        parameter = "@" + column.Name + " " + column.Type;

                                        break;

                                case "nchar":

                                        parameter = "@" + column.Name + " " + column.Type + "(" + column.Length + ")";

                                        break;

                                case "ntext":

                                        parameter = "@" + column.Name + " " + column.Type;

                                        break;

                                case "nvarchar":

                                        parameter = "@" + column.Name + " " + column.Type + "(" + column.Length + ")";

                                        break;

                                case "numeric":

                                        if (column.Scale.Length == 0)

                                                parameter = "@" + column.Name + " " + column.Type + "(" + column.Precision + ")";

                                        else

                                                parameter = "@" + column.Name + " " + column.Type + "(" + column.Precision + ", "+ column.Scale + ")";

                                        break;

                                case "real":

                                        parameter = "@" + column.Name + " " + column.Type;

                                        break;

                                case "smalldatetime":

                                        parameter = "@" + column.Name + " " + column.Type;

                                        break;

                                case "smallint":

                                        parameter = "@" + column.Name + " " + column.Type;

                                        break;

                                case "smallmoney":

                                        parameter = "@" + column.Name + " " + column.Type;

                                        break;

                                case "sql_variant":

                                        parameter = "@" + column.Name + " " + column.Type;

                                        break;

                                case "sysname":

                                        parameter = "@" + column.Name + " " + column.Type;

                                        break;

                                case "text":

                                        parameter = "@" + column.Name + " " + column.Type;

                                        break;

                                case "timestamp":

                                        parameter = "@" + column.Name + " " + column.Type;

                                        break;

                                case "tinyint":

                                        parameter = "@" + column.Name + " " + column.Type;

                                        break;

                                case "varbinary":

                                        parameter = "@" + column.Name + " " + column.Type + "(" + column.Length + ")";

                                        break;

                                case "varchar":

                                        parameter = "@" + column.Name + " " + column.Type + "(" + column.Length + ")";

                                        break;

                                case "uniqueidentifier":

                                        parameter = "@" + column.Name + " " + column.Type;

                                        break;

                                default:  // Unknow data type

                                        throw(new Exception("Invalid SQL Server data type specified: " + column.Type));

                        }

                       

                        // Return the new parameter string

                        if (checkForOutputParameter && (column.IsIdentity || column.IsRowGuidCol)) {

                                return parameter + " OUTPUT";

                        } else {

                                return parameter;

                        }

                }

 

 

M、生成数据操作方法的参数字符串

 

                internal static string CreateMethodParameter(Column column) {

                        string parameter;

                        string columnName;

 

                        // Format the column name

                        columnName = FormatCamel(column.Name);

               

                        switch (column.Type.ToLower()) {

                                case "binary":

                                        parameter = "byte[] " + columnName;

                                        break;

                                case "bigint":

                                        parameter = "Int64 " + columnName;

                                        break;

                                case "bit":

                                        parameter = "bool " + columnName;

                                        break;

                                case "char":

                                        parameter = "string " + columnName;

                                        break;

                                case "datetime":

                                        parameter = "DateTime " + columnName;

                                        break;

                                case "decimal":

                                        parameter = "decimal " + columnName;

                                        break;

                                case "float":

                                        parameter = "double " + columnName;

                                        break;

                                case "image":

                                        parameter = "byte[] " + columnName;

                                        break;

                                case "int":

                                        parameter = "int " + columnName;

                                        break;

                                case "money":

                                        parameter = "decimal " + columnName;

                                        break;

                                case "nchar":

                                        parameter = "string " + columnName;

                                        break;

                                case "ntext":

                                        parameter = "string " + columnName;

                                        break;

                                case "nvarchar":

                                        parameter = "string " + columnName;

                                        break;

                                case "numeric":

                                        parameter = "decimal " + columnName;

                                        break;

                                case "real":

                                        parameter = "float " + columnName;

                                        break;

                                case "smalldatetime":

                                        parameter = "DateTime " + columnName;

                                        break;

                                case "smallint":

                                        parameter = "short " + columnName;

                                        break;

                                case "smallmoney":

                                        parameter = "decimal " + columnName;

                                        break;

                                case "sql_variant":

                                        parameter = "object " + columnName;

                                        break;

                                case "sysname":

                                        parameter = "string " + columnName;

                                        break;

                                case "text":

                                        parameter = "string " + columnName;

                                        break;

                                case "timestamp":

                                        parameter = "DateTime " + columnName;

                                        break;

                                case "tinyint":

                                        parameter = "byte " + columnName;

                                        break;

                                case "varbinary":

                                        parameter = "byte[] " + columnName;

                                        break;

                                case "varchar":

                                        parameter = "string " + columnName;

                                        break;

                                case "uniqueidentifier":

                                        parameter = "Guid " + columnName;

                                        break;

                                default:  // Unknow data type

                                        throw(new Exception("Invalid SQL Server data type specified: " + column.Type));

                        }

                       

                        // Return the new parameter string

                        return parameter;

                }

 

 

N、生成DataReader所用到的参数方法

 

                internal static string CreateGetXxxMethod(Column column) {

                        switch (column.Type.ToLower()) {

                                case "binary":

                                        return "GetBytes";

                                case "bigint":

                                        return "GetInt64";

                                case "bit":

                                        return "GetBoolean";

                                case "char":

                                        return "GetChars";

                                case "datetime":

                                        return "GetDateTime";

                                case "decimal":

                                        return "GetDecimal";

                                case "float":

                                        return "GetFloat";

                                case "image":

                                        return "GetBytes";

                                case "int":

                                        return "GetInt32";

                                case "money":

                                        return "GetDecimal";

                                case "nchar":

                                        return "GetString";

                                case "ntext":

                                        return "GetString";

                                case "nvarchar":

                                        return "GetString";

                                case "numeric":

                                        return "GetDecimal";

                                case "real":

                                        return "GetDecimal";

                                case "smalldatetime":

                                        return "GetDateTime";

                                case "smallint":

                                        return "GetInt16";

                                case "smallmoney":

                                        return "GetFloat";

                                case "sql_variant":

                                        return "GetBytes";

                                case "sysname":

                                        return "GetString";

                                case "text":

                                        return "GetString";

                                case "timestamp":

                                        return "GetDateTime";

                                case "tinyint":

                                        return "GetByte";

                                case "varbinary":

                                        return "GetBytes";

                                case "varchar":

                                        return "GetString";

                                case "uniqueidentifier":

                                        return "GetGuid";

                                default:  // Unknow data type

                                        throw(new Exception("Invalid SQL Server data type specified: " + column.Type));

                        }

                }

 

O、格式化名字，第一个字母大写，其余小写，使用Pascal的命名习惯

                internal static string FormatPascal(string original) {

                        if (original.Length > 0) {

                                return Char.ToUpper(original[0]) + original.Substring(1);

                        } else {

                                return String.Empty;

                        }

                }

 

P、格式化名字，第一个字母小写，其余大写

                internal static string FormatCamel(string original) {

                        if (original.Length > 0) {

                                return Char.ToLower(original[0]) + original.Substring(1);

                        } else {

                                return String.Empty;

                        }

                }

 

Q、格式化类名，以表的名字生成类名，把第一字母变为大写

                internal static string FormatClassName(string tableName) {

                        string className;

                       

                        if (Char.IsUpper(tableName[0])) {

                                className = tableName;

                        } else {

                                className = FormatPascal(tableName);

                        }

                       

                        // Attept to removing a trailing 's' or 'S', unless, the last two characters are both 's' or 'S'.

                        if (className[className.Length - 1] == 'S' && className[className.Length - 2] != 'S') {

                                className = className.Substring(0, className.Length - 1);

                        } else if (className[className.Length - 1] == 's' && className[className.Length - 2] != 's') {

                                className = className.Substring(0, className.Length - 1);

                        }

                       

                        return className;

                }

 

R、规整SqlServer的数据类型

                internal static string GetSqlDbType(string sqlDbType) {

                        switch (sqlDbType.ToLower()) {

                                case "binary":

                                        return "Binary";

                                case "bigint":

                                        return "BigInt";

                                case "bit":

                                        return "Bit";

                                case "char":

                                        return "Char";

                                case "datetime":

                                        return "DateTime";

                                case "decimal":

                                        return "Decimal";

                                case "float":

                                        return "Float";

                                case "image":

                                        return "Image";

                                case "int":

                                        return "Int";

                                case "money":

                                        return "Money";

                                case "nchar":

                                        return "NChar";

                                case "ntext":

                                        return "NText";

                                case "nvarchar":

                                        return "NVarChar";

                                case "numeric":

                                        return "Decimal";

                                case "real":

                                        return "Real";

                                case "smalldatetime":

                                        return "SmallDateTime";

                                case "smallint":

                                        return "SmallInt";

                                case "smallmoney":

                                        return "SmallMoney";

                                case "sql_variant":

                                        return "Variant";

                                case "sysname":

                                        return "VarChar";

                                case "text":

                                        return "Text";

                                case "timestamp":

                                        return "Timestamp";

                                case "tinyint":

                                        return "TinyInt";

                                case "varbinary":

                                        return "VarBinary";

                                case "varchar":

                                        return "VarChar";

                                case "uniqueidentifier":

                                        return "UniqueIdentifier";

                                default:  // Unknow data type

                                        throw(new Exception("Invalid SQL Server data type specified: " + sqlDbType));

                        }

                }

 

S、生成参数变量格式 如：new SqlParameter(@test,@Test)

                internal static string CreateSqlParameter(Column column) {

                        return "new SqlParameter(/"@" + column.Name + "/", " + FormatCamel(column.Name) + ")";

                }