Good day
I am creating a forms application to create a database and tables in sql. (This is just a side project i am doing)
I create dynamic text boxes for each column and table that the user wants to create.
Creating a table actually works fine when it is only one table with its columns that need to be created. It is when I want to create more than one table that it does not work. I know I am just missing something somewhere. but for the life of me I cant figure out what it is.
As in the code: i is incremented for each time a column text box is added.
j is incremented for each time a table text box is added.
when j is incremented i is set to 0.
added is incremented each time the Stringbuilder is appended.
If needed the full source code is located here:
https://www.dropbox.com/s/rtnqlsukw5kp86h/Create%20Databases%20Source.doc[
^]
Thank you in advance
try
{
sqlCon = new SqlConnection("Data Source='" + server + "';Initial Catalog=" + Datbase + ";Integrated Security=SSPI");
sqlCon.Open();
build = new StringBuilder();
build.Append("USE " + Datbase + "\r\n");
foreach (string cont in contents)
{
foreach (Control ctrl in this.Controls)
{
if (ctrl.Name != "")
{
if (ctrl.Name == cont)
{
strTable = ctrl.Text.Replace(' ', '_');
build.Append("IF NOT EXISTS ( SELECT * FROM sysobjects WHERE name = '" + strTable + "' and xtype = 'U')\r\n");
build.Append("CREATE TABLE " + strTable + "\r\n");
build.Append("(\r\n");
}
if (ctrl.Name.Contains(cont) && ctrl.Name != cont)
{
if (ctrl.Name.Contains("Column"))
{
strColumn = ctrl.Text.Replace(' ', '_');
}
if (ctrl.Name.Contains("Type"))
{
strType = ctrl.Text.Replace(' ', '_');
}
if (ctrl.Name.Contains("null"))
{
if (ctrl.Text == "NN")
{
strNull = "NOT NULL";
}
if (ctrl.Text == "N")
{
strNull = "NULL";
}
}
if (ctrl.Name.Contains("IDEN"))
{
strIden = "IDENTITY";
}
if (IDEN.Checked == true && strColumn != null && strType != null && strNull != null && strIden != null && add == 0)
{
Primary = strColumn;
if (i == 2)
{
build.Append(strColumn + " " + strType + " " + strNull + " " + strIden + "\r\n");
build.Append("PRIMARY KEY(" + Primary + ")");
build.Append(")\r\n");
}
else
{
build.Append(strColumn + " " + strType + " " + strNull + " " + strIden + ",\r\n");
}
strType = null;
strNull = null;
strIden = null;
add++;
}
else
{
if (strIden == null && strNull != null && !ctrl.Name.Contains("Column1 ") && !ctrl.Name.Contains("Type1 ") && !ctrl.Name.Contains("nulls1 ") && !ctrl.Name.Contains("notnull1 ") && !ctrl.Name.Contains("IDEN1 ") && strType != null)
{
if (add < i)
{
if (add == (i - 2))
{
build.Append(strColumn + " " + strType + " " + strNull + "\r\n");
build.Append("PRIMARY KEY(" + Primary + ")\r\n");
build.Append(")\r\n");
}
else if (add < (i - 2))
{
build.Append(strColumn + " " + strType + " " + strNull + ",\r\n");
strNull = null;
}
add++;
}
}
}
}
}
}
add = 0;
}
MessageBox.Show(build.ToString());
cmd = new SqlCommand(build.ToString(), sqlCon);
cmd.ExecuteNonQuery();
sqlCon.Close();
add = 0;
MessageBox.Show(strTable + " Added");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
sqlCon.Close();
add = 0;
}
}