Hello to everybody, again. Today I will post the example of how we can transfering schema without using SMO(SqlServer).
You can ask "man why this post in English? Your english is awful". And I can answer to you people : "I want to practice in it". So, let's begin.
private void TestFunction(string dbNameFrom, string dbNameTo)
{
string cnnString = "server=" + Server + ";user id=" + UserId
+ ";password=" + Password + ";Initial Catalog = " + dbNameFrom
+ ";Connect Timeout=30;Integrated Security=False;"; // for exaple
using (System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection(cnnString))
{
cnn.Open();
for (int i = 0; i < schemaTables.Length; i++)
{
string tbName = schemaTables[i];
string[] schemaCondition = { null, null, tbName };
DataTable tbFrom = cnn.GetSchema("Columns", schemaCondition);
DataTable tbTo = null;
string cnnStringTo = "server=" + Server + ";user id=" + UserId
+ ";password=" + Password + ";Initial Catalog = " + dbNameTo
+ ";Connect Timeout=30;Integrated Security=False;";
using (System.Data.SqlClient.SqlConnection cnnTo = new System.Data.SqlClient.SqlConnection(cnnStringTo))
{
cnnTo.Open();
string[] schemaConditionTo = { null, null, tbName };
tbTo = cnnTo.GetSchema("Columns", schemaConditionTo);
}
AlterTable(tbFrom, tbTo, tbName, dbNameTo); // at this function we will be altering our table
}
// insert data function will be here
//for (int i = 0; i < dataTables.Length; i++)
//{
// TODO at next post
//}
}
}
private void AlterTable(DataTable tbFrom, DataTable tbTo, string tbName, string dbNameTo)
{
string sqlStatement = "";
string colName = "";
string colDataType = "";
string val = "";
for (int i = 0; i <
tbFrom.Rows.Count; i++)
{
DataRow r =
tbFrom Rows[i];
colName = r["Column_Name"].ToString();
if(ColumnExists(colName, tbTo))
{
continue;
}
sqlStatement = "ALTER TABLE [dbo].[" + tbName + "] ADD ";
colDataType = r["Data_Type"].ToString();
GetLength(colDataType, ref val, r);
sqlStatement += colName + " " + colDataType + val;
// You can use connection from the previos method. Decided to make a new connection.
// Only God will judge me.)
string cnnStringTo = "server=" + Server + ";user id=" + UserId
+ ";password=" + Password + ";Initial Catalog = " + dbNameTo
+ ";Connect Timeout=30;Integrated Security=False;";
using (System.Data.SqlClient.SqlConnection cnnTo = new System.Data.SqlClient.SqlConnection(cnnStringTo))
{
cnnTo.Open();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sqlStatement, cnnTo);
cmd.ExecuteNonQuery();
sqlStatement = "";
}
}
}
private void GetLength(string type, ref string val, DataRow r)
{
val = "";
switch (type)
{
case "varchar":
val = "(" + r["Character_Maximum_Length"].ToString() + ")"; // for example
break;
case "float":
break; // and so on
case "int":
break;
case "datetime":
break; // and so on
case "bit":
break; // and so on
}
// Here perhaps, you will be adding "allow nulls" property or not. I don't need it.
}
For this moment I dont know exactly how fast is this function work's. Perhaps tomorrow, I will post the speed, of altering at the "Transfer Data" post.
Thank's to all, who is reading my post's. And for the other people, you dont miss nothing.
