How can i retrive all grdiview columns values in one iteration without loop?

Question

I'm facing problem in creating table programtically in asp.net c#. I'm working on sql project. I have a gridview and a button,When I click on button then I want that all gridview columns values e.g column_name,data type,allowNull,PrimaryKey etc.

All values inserted inside the "Create Table QUERY" in one iteration and table will be created, But I have a problem. I'm using for loop when loop first time execute then only one row iterate and table created in SQL only one column(Just first row) and when 2nd iteration execute then table name will be same ,so there is a issue.

Kindly tell me how can I resolve this issue.All values successfully inserted into the table but problem is in creating table. Table is created but 'ONLY LAST ' row table is created,Table contain only one row.How can i resolve this issue.

How can i do this?

Here is my "button" code aspx.cs`

public void insert(object sender, EventArgs e)
 {
   SqlConnection cnn = new SqlConnection("Data Source=HAMEED_KHAN\\SQLEXPRESS;Initial Catalog=db_compiler;Integrated Security=True");
    string d=Session["value"].ToString();
    SqlCommand cmd2=new SqlCommand("SELECT Database_id FROM Create_db WHERE Database_Name='"+d+"'",cnn);
    cnn.Open(); 
    string dbid = cmd2.ExecuteScalar().ToString();
    cnn.Close();
    int D_ID = Int32.Parse(dbid);
    string str = "";
    string type = "";
    for (int i = 0; i < GridView2.Rows.Count; i++)
    {
        string tblname = "abc";
        str=GridView2.Rows[i].Cells[1].Text.ToString();
        type=GridView2.Rows[i].Cells[2].Text.ToString();
        string Name = GridView2.Rows[i].Cells[1].Text.ToString();
        string Type = GridView2.Rows[i].Cells[2].Text.ToString();
        CheckBox allow=GridView2.Rows[i].Cells[3].Controls[0]as CheckBox;
        CheckBox primary = GridView2.Rows[i].Cells[4].Controls[0] as CheckBox;
        string s = Session["UID"].ToString();
      int id = Int32.Parse(s);
        string date = DateTime.Now.ToString();
        string A = (allow.Checked == true ? "NULL" : "NOT NULL");
        string P = (primary.Checked == true ? "PRIMARY KEY" : "");
       // string query="USE "+d+" CREATE TABLE ABCD ("+Name+" "+Type+" "+A+")";
      //  SqlCommand cmd3 = new SqlCommand(query, cnn);
        SqlCommand cmd = new SqlCommand("insertTbl", cnn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Name", tblname);
        cmd.Parameters.AddWithValue("@col_name", Name);
        cmd.Parameters.AddWithValue("@dtype",Type);
        cmd.Parameters.AddWithValue("@dbId", D_ID);
        cmd.Parameters.AddWithValue("@allow",(allow.Checked==true ? "true" : "false"));
        cmd.Parameters.AddWithValue("@primary", (primary.Checked == true ? "true" : "false"));
        cmd.Parameters.AddWithValue("@user", id);
        cmd.Parameters.AddWithValue("@date", date);
        SqlDataAdapter ad = new SqlDataAdapter(cmd);
        cnn.Open();
        cmd.ExecuteNonQuery();
       // cmd3.ExecuteNonQuery();
        cnn.Close();
    }
    string str1=str;
    string str2=type;
    //string AA="ALLOW NULL";
   // string queryy =string.Format(@"USE {"+d+"}; IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA = 'dbo' AND  TABLE NAME = 'ABCDE'))CREATE TABLE ABCDE ({"+str1+"} {"+type+"} {"+AA+"})");
    string queryy="USE "+d+" If not exists (select name from sysobjects where name = 'Customers') CREATE TABLE Customers("+str1+" "+type+")";
    SqlCommand cmd4 = new SqlCommand(queryy, cnn);
    cnn.Open();
    cmd4.ExecuteNonQuery();
    cnn.Close();
  }

score 0 · Answer 1 · edited May 23 '17 at 12:16

0

You should make sure to avoid recreating the same table by using a rerunnable script. For CREATE TABLE, checking its existence is done this way:

string query = String.Format(@"
USE {0}; 
IF (NOT EXISTS (SELECT * 
                FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = '{1}'))
    CREATE TABLE {1} ({2} {3} {4})", d, "ABCD", Name, Type, A);

Notice that I have used String.Format to increase readability and avoid string concatenation (strings are immutable, so many instances are created when using + operator).

However, consider moving your CREATE TABLE outside of for loop, if your intention is to create once and insert multiple times. Anyway, existence check should be performed.

From C# 6.0, you can use both verbatim and interpolation (actually, string interpolation was introduced in 6.0). Something like this:

string query = $@"
USE {d}; 
IF (NOT EXISTS (SELECT * 
                FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = 'ABCD'))
    CREATE TABLE ABCD ({Name} {Type} {A})";

edited May 23 '17 at 12:16

Community

1
1

answered Apr 16 '16 at 05:46

Alexei - check Codidact

22,016
16
145
164

Thanks Alexei for replying my question.Ok let me check – Saira Apr 16 '16 at 05:49
But here is one problem if i write 'CREATE TABLE ' query outside the loop then how values come from gridview,because I'm reteriving values from gridview using loop.Such as 'Name', 'Type','A' values comes from gridview etc. – Saira Apr 16 '16 at 05:53
Alexei please reply me.It's giving me error,,,query string error – Saira Apr 16 '16 at 08:22
Can you place a breakpoint after query is computed and provide its value? – Alexei - check Codidact Apr 16 '16 at 11:51
how can i access loop inside variables outside the loop?Because,my query is outside the loop according to you,so i m trying – Saira Apr 16 '16 at 12:10
I apply breakpoints ,here us error screen shot http://hostthenpost.org/uploads/b37129ef14f01171d5b89bb8e02367b8.png – Saira Apr 16 '16 at 12:31
From your error it looks like your using both { } and string concatenation. Are you using `String.Format` or normal concatenation? – Alexei - check Codidact Apr 16 '16 at 13:29
I'm using string query.Table is created in the database but only one column is created in the table..Here is my query outside the loop `string str1=str; string str2=type; string queryy="USE "+d+" If not exists (select name from sysobjects where name = 'Customers') CREATE TABLE Customers("+str1+" "+type+")"; ` – Saira Apr 16 '16 at 14:14

How can i retrive all grdiview columns values in one iteration without loop?

1 Answers1