Sometimes we need to fetch data into a
datatable and insert some columns into a mysql table. If columns of mysql table is predefined then we can insert data into mysql table
easily but in some case we have to select a table and find the columns of that
table at runtime and mapped the fields and insert data from datatable.
This situation may happen when we are transfering data from one database server
to another database server using a Asp.net web application.
Here we have to select two database server
,databases, and select source table with unknown column list and same as
destination column list.
In that case we can not fix the variable to
our code.
Here we can use this function
first mapping store into a 2-d array and then call this function.
columnlist =[mysqlfield,datatablefield] is a 2-d string array where mapping is
given at runtime.
ex={[mysqlid,userid],[mysql_Username,dt_username],[MysqlLname,Dtlname]}
public
void getdata_mstomy(string[,]
columlist, string D_Conn,
string D_Table,DataTable dt)
{
string
mysqlcommand="insert into "+D_Table+"
(";
for
(int x = 0; x < columlist.Length / 2; x++)
{
if
(columlist[x, 1] != null)
{
mysqlcommand = mysqlcommand +
columlist[x, 1].ToString() + ",";
}
}
int
len = mysqlcommand.Length;
mysqlcommand =
mysqlcommand.Substring(0, len - 1)+") values (";
string
temp_comm = mysqlcommand;
for
(int i = 0; i < dt.Rows.Count; i++)
{
mysqlcommand = temp_comm;
for
(int x = 0; x < columlist.Length / 2; x++)
{
if
(columlist[x, 0] != null)
{
mysqlcommand = mysqlcommand +
"'"+dt.Rows[i][columlist[x, 0].ToString()]+"'"
+ ",";
}
}
len = mysqlcommand.Length;
mysqlcommand =
mysqlcommand.Substring(0, len - 1)+")";
MySqlConnection conn =
new MySqlConnection(D_Conn);
MySqlCommand mycmd =
new MySqlCommand(mysqlcommand,conn);
conn.Open();
mycmd.ExecuteNonQuery();
conn.Close();
}
}
where columnlist =[mysqlfield,datatablefield]
is a 2-d string
array where mapping is given at runtime.
ex={[mysqlid,userid],[mysql_Username,dt_username],[MysqlLname,Dtlname]}