How to convert dataTable into script and save it in to table using C#

Question

0.00/5 (No votes)

See more:

Dear All,

i need your help
i have one query i was select some data from client database and want to save in internal database.
client database is mysql and our database is mssql so on single query i want to get select script and insert script both script want to save in mssql table on single save button click invent some process done 50 time and save all script in table.So pls let me know if some one know it i need heelp on priority.

Posted 6-Feb-14 19:04pm

Arjunwalmiki

Add a Solution

Comments

V. 7-Feb-14 3:17am

Can you try to clear out your question a bit, because it is very hard to understand what the exact problem is.
Also add what you have done and where you are stuck. What is the exact problem?
(PS: just update this question, you don't need to post a new one)

Arjunwalmiki 7-Feb-14 3:47am

hi
this is my code

protected void btnDatabaseMigration_Click(object sender, EventArgs e)
{
string str = string.Empty;
DataSet ds1 = new DataSet();
DataView dv;

foreach (ListItem li in TableCheckBoxList.Items)
{
if (li.Selected)
{
MySqlConnection myconn3;
string abc = "";
abc = "Data Source=localhost;Initial Catalog=" + lblMySqlDB.Text + ";user id=root;password=root";
myconn3 = new MySqlConnection(abc);
myconn3.Open();
str = li.ToString();
string query = "Select " + str + " from " + lblMySqlDB.Text + "." + Label5.Text + "";
MySqlDataAdapter mydata = new MySqlDataAdapter(query, myconn3);
DataTable dTable = new DataTable();
mydata.Fill(ds1, str);
dv=ds1.Tables[0].DefaultView;
DataTable table = ds1.Tables[0];
dv.Sort = "" + str + " DESC";
myconn3.Close();

}

// int a = 0;
foreach (DataTable dt in ds1.Tables)
{

}
if (ds1.Tables.Count > 0)
{
string strForSqlServer = string.Empty;
DataSet dsForSqlServer = new DataSet();
foreach (ListItem listOFSQL in CheckBoxIntellectTableData.Items)
{
if (listOFSQL.Selected)
{
SqlConnection sqlconForMigration;
// string abc = "";
// abc = "Data Source=localhost;Initial Catalog=" + lblSqlDatabase.Text + ";user id=root;password=root";
foreach (DataRow dr in ds1.Tables[0].Rows)
{
//for (int i = 0; i < dr.Table.Columns.Count; i++)
//{
sqlconForMigration = new SqlConnection(strIn);
sqlconForMigration.Open();
strForSqlServer = listOFSQL.ToString();
string query = "insert into " + lblSqlTableName.Text + "(" + strForSqlServer + ") values ('" + dr[0].ToString() + "')";
SqlCommand cmd = new SqlCommand(query, sqlconForMigration);
cmd.ExecuteNonQuery();
sqlconForMigration.Close();
//}
}

}
}
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Record Added In MSSQL Table')", true);
}

// a++;

}

}

in this code i am select data from mysql server and insert in to mssql table but i want to save 10 to 20 script just like this and later want to execute 10 to 20 script daily or as per requirement

V. 7-Feb-14 3:50am

please update the code in the question (use the "pre" tags!), there is no formatting in the comments :-)

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)

Arjunwalmiki · Answer 1 · 2014-02-06T22:00:00

C#

protected void btnDatabaseMigration_Click(object sender, EventArgs e)
    {
         string str = string.Empty;
        DataSet ds1 = new DataSet();
        DataView dv;
        
        foreach (ListItem li in TableCheckBoxList.Items)
        {
            if (li.Selected)
            {
                MySqlConnection myconn3;
                string abc = "";
                abc = "Data Source=localhost;Initial Catalog=" + lblMySqlDB.Text + ";user id=root;password=root";
                myconn3 = new MySqlConnection(abc);
                myconn3.Open();
                str = li.ToString();
                string query = "Select " + str + " from " + lblMySqlDB.Text + "." + Label5.Text + "";
                MySqlDataAdapter mydata = new MySqlDataAdapter(query, myconn3);
                DataTable dTable = new DataTable();
                mydata.Fill(ds1, str);
                dv=ds1.Tables[0].DefaultView;
                DataTable table = ds1.Tables[0];
                dv.Sort = "" + str + " DESC";
                myconn3.Close();
               
            }
           

          //  int a = 0;
            foreach (DataTable dt in ds1.Tables)
            { 
                
            }
            if (ds1.Tables.Count > 0)
            {
                string strForSqlServer = string.Empty;
                DataSet dsForSqlServer = new DataSet();
                foreach (ListItem listOFSQL in CheckBoxIntellectTableData.Items)
                {
                    if (listOFSQL.Selected)
                    {
                        SqlConnection sqlconForMigration;
                        // string abc = "";
                        //  abc = "Data Source=localhost;Initial Catalog=" + lblSqlDatabase.Text + ";user id=root;password=root";
                        foreach (DataRow dr in ds1.Tables[0].Rows)
                        {
                            //for (int i = 0; i < dr.Table.Columns.Count; i++)
                            //{
                            sqlconForMigration = new SqlConnection(strIn);
                            sqlconForMigration.Open();
                            strForSqlServer = listOFSQL.ToString();
                            string query = "insert into " + lblSqlTableName.Text + "(" + strForSqlServer + ") values ('" + dr[0].ToString() + "')";
                            SqlCommand cmd = new SqlCommand(query, sqlconForMigration);
                            cmd.ExecuteNonQuery();
                            sqlconForMigration.Close();
                            //}
                        }

                    }
                }
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Record Added In MSSQL Table')", true);
            }

               // a++;
                  
            }
      

        }