Transfering schema and data (SQL Server ), without SMO. Using only System.Data.SqlClient. First part (Transfering schemas)

   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.