Generate SQL server table schema from tally database at run time in asp.net with c# DataTable

Question

0.00/5 (No votes)

See more:

C#

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 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.

Posted 18-Jan-16 0:59am

NANDKUMAR GAIKWAD

Updated 5-Jul-18 0:32am

Manas_Kumar

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manas_Kumar · Accepted Answer · 2016-01-18T01:53:00

Solution 1

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:

C#

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);

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

Posted 18-Jan-16 1:53am

Manas_Kumar

Updated 18-Jan-16 18:14pm

v3

Comments

NANDKUMAR GAIKWAD 19-Jan-16 6:47am

thanx for suggestion.
and i'm new to here..but this is not excuse..
your answer is fabulous..
i send post my code and tell me what is wrong with that

[no name] 19-Jan-16 7:36am

What is your actual problem: either generate database from Tally schema or binding data to drop downlist?

NANDKUMAR GAIKWAD 19-Jan-16 7:42am

there is no problem with binding i'm trying i get schema from my below code

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 want how to generate table in sql server 2008 with this schema of table and there column name and there data type
but when i use your code i get that schema's datatable information

[no name] 19-Jan-16 7:55am

If you are getting DataTable and its columns properly then follow my answer to generate SQL Query. Once you get SQL Query you need to execute in database.

Are you getting table schema properly from con variable?

NANDKUMAR GAIKWAD 19-Jan-16 8:01am

yes i'm getting proper schema from i tried code..
but when i trying for single table or multiple table it not generate script

[no name] 19-Jan-16 8:02am

What does it mean: but when i trying for single table or multiple table it not generate script

NANDKUMAR GAIKWAD 20-Jan-16 1:41am

i use your code as follow

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
//Label2.Text = "Table Name : " + Convert.ToString(ListBox1.SelectedItem.Text);
Label2.Text = "table name: " + Convert.ToString(DropDownList1.SelectedItem.Text);
//DataSet objds = fetchTallyTableContent(Convert.ToString(ListBox1.SelectedItem.Text));
DataSet objds = fetchTallyTableContent(Convert.ToString(DropDownList1.SelectedItem.Text));
GridView2.DataSource = objds;
GridView2.DataBind();
// Call the method
//DataTable yourDataTableObj = new DataTable();
//string sqlQuery = CreateTABLE(yourDataTableObj);
DataTable dt = new DataTable(Convert.ToString(DropDownList1.SelectedItem.Text));
string sqlQuery = CreateTABLE(dt);
textbox1.Text = sqlQuery;
}

public DataSet fetchTallyTableContent(string tblName)
{
//string query = "SELECT * FROM Techweb.TallyUser." + tblName + " " + tblName;
string query = "SELECT * FROM DatavisionITC.TallyUser." + tblName + " " + tblName;
string source = "PORT=9000;DRIVER=Tally ODBC Driver;SERVER={(local)}";
OdbcConnection con = new OdbcConnection(source);
OdbcCommand cmd = new OdbcCommand(query, con);
con.Open();
OdbcDataAdapter da = new OdbcDataAdapter(query, con);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
return ds;
}
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)";
}
}

i get table name but now problem is that output look like this
where i exactly mistaking i cant find out
"CREATE TABLE State
)"
this is an output

[no name] 20-Jan-16 2:09am

It looks like problem with table.Columns.Count Can you just debug and see what is the value you are getting.

NANDKUMAR GAIKWAD 20-Jan-16 3:00am

yes i debug and getting only output
"CREATE TABLE Table
)"
this is an outpu
can i send you full code with aspx
that you can debug you side
i cant find out exact problem

NANDKUMAR GAIKWAD 20-Jan-16 3:09am

i done it i'll send you what i done

[no name] 20-Jan-16 4:03am

What was the actual problem?

NANDKUMAR GAIKWAD 21-Jan-16 6:05am

i have a question manas it is possible that we can insert data into database without using insert statement
because right now i create database as per your answer but now i need to insert into database without insert statment

[no name] 21-Jan-16 7:30am

yes, we can do it through SQL Bulk copy. Follow below URL:

http://www.codeproject.com/Articles/18418/Transferring-Data-Using-SqlBulkCopy

http://dotnetmentors.com/c-sharp/bulk-upload-into-sql-server-using-sqlbulkcopy-and-c-sharp.aspx

NANDKUMAR GAIKWAD 21-Jan-16 23:31pm

thanx manas i'm trying on you answer

NANDKUMAR GAIKWAD 22-Jan-16 3:38am

total summary what i done over here
1) get schema of tally database
2) create table structure script as per your code
3) for that table structure i used your link of bulk copy and save database to sql server(here i at the same time of table creation and culk copy used).

thanx for your help it never possible that i could made it

[no name] 22-Jan-16 3:44am

You are welcome buddy :) Hope you implemented a GREAT task.

NANDKUMAR GAIKWAD 22-Jan-16 3:51am

its all to you..sir ji

Member 13766988 7-Apr-18 3:45am

I'm not able to get schema for tally database

string source = "PORT=9000;DRIVER=Tally ODBC Driver;SERVER={(local)}";
OdbcConnection con = new OdbcConnection(source);
con.Open();

After exceuting these line of code.I'm getting an exception as mentioned below,
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Can someone please help me out to try this solution

shivakad 28-Jan-21 10:42am

please open tally folder and run regodbc32.exe as administrator. will work smothly