使用c＃DataTable在asp.net中运行时从tally数据库生成SQL Server表模式 [英] Generate SQL server table schema from tally database at run time in asp.net with c# DataTable

问题描述

string source = "PORT=9000;DRIVER=Tally ODBC Driver;SERVER={(local)}";
            OdbcConnection con = new OdbcConnection(source);
            con.Open();
            DataTable dt = new DataTable();
            string[] restrictions = new string[1];
            dt = con.GetSchema("Tables");
            Database db = new Database();

            DropDownList1.DataSource = dt;
            DropDownList1.DataTextField = "Table_Name";
            DropDownList1.DataValueField = "Table_Name";
            DropDownList1.DataBind();

i从这得到tally架构，但我想使用这个架构并在sql server 2008中使用创建数据库，

请帮助我，

如果我的问题不理解那么再问我一次。

thanx。

i get tally schema from this but i want to use this schema and create database in sql server 2008 using,
kindly help me,
if my question is not understanding then ask me again.
thanx.

推荐答案

步骤1 ：创建一个静态方法，该方法将数据表作为参数并提取所有coulmns，即其数据类型。然后它生成sql quries。以下是方法：

Step 1: Create a static method which will take datatable as parameter and extract all coulmns, its datatype. Then it generates sql quries. Here is the method:

public static string CreateTABLE(DataTable table)
{
    string sqlQuery;
    sqlQuery = "CREATE TABLE " + table.TableName + "(";
    for (int i = 0; i < table.Columns.Count; i++)
    {
        sqlQuery += "\n [" + table.Columns[i].ColumnName + "] ";
        string columnType = table.Columns[i].DataType.ToString();

        // You will get different data type in  C#(you need to change it SQLServer):Boolean,Byte,Char,DateTime,Decimal,Double,Int16,Int32,Int64,SByte,Single, String,TimeSpan,UInt16,UInt32,UInt64
        switch (columnType)
        {
            case "System.Int32":
                sqlQuery += " int ";
                break;
            case "System.Int64":
                sqlQuery += " bigint ";
                break;
            case "System.Int16":
                sqlQuery += " tinyint ";
                break;
            case "System.Decimal":
                sqlQuery += " decimal ";
                break;
            case "System.DateTime":
                sqlQuery += " datetime ";
                break;
            case "System.String":
            default:
                sqlQuery += string.Format(" nvarchar({0}) ", table.Columns[i].MaxLength == -1 ? "max" : table.Columns[i].MaxLength.ToString());
                break;
        }

        if (table.Columns[i].AutoIncrement)
            sqlQuery  += " IDENTITY(" + table.Columns[i].AutoIncrementSeed.ToString() + "," + table.Columns[i].AutoIncrementStep.ToString() + ") ";

        if (!table.Columns[i].AllowDBNull)
            sqlQuery += " NOT NULL ";
        sqlQuery += ",";
    }

    return sqlQuery.Substring(0, sqlQuery.Length-1) + "\n)";
}

// Call the method
DataTable yourDataTableObj = new DataTable();
string sqlQuery = CreateTABLE(yourDataTableObj);

第2步 ：一旦调用该方法，您将获得SQL查询。收集它并手动在sqlserver中执行它，或者你可以通过ADO.NET执行

Step 2: Once you call the method you will get SQL query. Collect it and execute it in sqlserver manually or you can execute through ADO.NET

这篇关于使用c＃DataTable在asp.net中运行时从tally数据库生成SQL Server表模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！