how can I generalize my code to rename column names via asp.net

Question

I create a web form that contains: Dropdownlist, texbox and rename button. The general idea is that Dropdownlist contains list of column names of one table in my database. Then the user select one of these names and enter the new name in the textbox. After that, he click the rename button. The result is rename the selected column in my database. My code is work well. And it is give exact result. see my code:

protected void Button1_Click(object sender, EventArgs e)
{
   string conString = @"Data Source=FATTO-TOSH\SQLEXPRESS;Initial Catalog=Positions;Integrated Security=True";

   if (DropDownList1.SelectedIndex == 0)
   using (var con = new SqlConnection(conString))
   {
      var cmd = new SqlCommand("sys.sp_rename", con);
      con.Open();
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.AddWithValue("@objname", "PositionsReq.skill1")
                    .SqlDbType = SqlDbType.NVarChar;
      cmd.Parameters.AddWithValue("@newname", name.Text)
                    .SqlDbType = SqlDbType.NVarChar;
      cmd.ExecuteNonQuery();
   }
   if (DropDownList1.SelectedIndex == 1)
   using (var con = new SqlConnection(conString))
   {
      var cmd = new SqlCommand("sys.sp_rename", con);
      con.Open();
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.AddWithValue("@objname", "PositionsReq.skill2")
                    .SqlDbType = SqlDbType.NVarChar;
      cmd.Parameters.AddWithValue("@newname", name.Text)
                    .SqlDbType = SqlDbType.NVarChar;
      cmd.ExecuteNonQuery();
   }
   if (DropDownList1.SelectedIndex == 2)
   using (var con = new SqlConnection(conString))
   {
      var cmd = new SqlCommand("sys.sp_rename", con);
      con.Open();
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.AddWithValue("@objname", "PositionsReq.skill3")
                    .SqlDbType = SqlDbType.NVarChar;
      cmd.Parameters.AddWithValue("@newname", name.Text)
                    .SqlDbType = SqlDbType.NVarChar;
      cmd.ExecuteNonQuery();
   }
}

My question is: The modification achieved only one time (regard to one column) because when I change the name from skill1 to Sk for example. May in other time, the user want to modify Sk to other name. the code doesn't work because it is initialize as column name is skill1 only. do you have an idea how to generalize the code to work whatever the name of the column? thank you

You really should be calling `.Close()` on your connection objects. That could be part of your problem right there... — Brian, Dec 06 '13 at 19:37
@Brian - the `using` blocks for the connection objects are doing that implicitly for the OP. The `IDisposable` implementation for `SqlConnection` both closes and disposes of the connection object. — Karl Anderson, Dec 06 '13 at 19:38
I am not sure I get your question correctly, but this is what I understood. You need to bind the Dropdownlist again with new data after changing the column name to be able to change it again to something else. — Poornima, Dec 06 '13 at 19:39
Why do you want your users to be able to alter the database? Are you really trying to just alias the column names to the user's desired output for a report or something like that? — Karl Anderson, Dec 06 '13 at 19:39
@KarlAnderson - Yes, I know that the `using` blocks do that. But, my point was that it is _a good practice_ to use `.Close()` regardless. — Brian, Dec 06 '13 at 19:51
@Brian no it isn't - where are you getting that from? A `using` block *guarantees* that `.Close` will be called, calling `.Close` directly does not. There is absolutely no point. — Dai, Dec 06 '13 at 19:57
@Brian - if an exception happens before your explicit `.Close()` call, then the generated `finally` block from the `using` will call the `.Close()` in the `Dispose()` method, so it is redundant to explicitly call `.Close()` inside of a `using` block. — Karl Anderson, Dec 06 '13 at 20:02
@KarlAnderson - You just taught me something I didn't know, +1 and thanks. — Brian, Dec 06 '13 at 20:06

score 0 · Answer 1 · answered Dec 06 '13 at 19:55

Instead of using DropDownList1.SelectedIndex why don't you have the name of the column you want it to be changed to listed in the DropDownList1 and then use DropDownList1.SelectedValue like so:

protected void Button1_Click(object sender, EventArgs e)
{
   string conString = @"Data Source=FATTO-TOSH\SQLEXPRESS;Initial Catalog=Positions;Integrated Security=True";

   using (var con = new SqlConnection(conString))
   {
       var cmd = new SqlCommand("sys.sp_rename", con);
       con.Open();
       cmd.CommandType = CommandType.StoredProcedure;
       cmd.Parameters.AddWithValue("@objname", DropDownList1.SelectedValue)
                     .SqlDbType = SqlDbType.NVarChar;
       cmd.Parameters.AddWithValue("@newname", name.Text)
                     .SqlDbType = SqlDbType.NVarChar;
       cmd.ExecuteNonQuery();
   }
}

MSDN

Make sure you toss in some null checking on the SelectedValue before you actually execute any commands.

I think the same problem will be appeare. The query select exactly column with name skill1 only. but when I change it to other name, the query doesn't work. May I didn't understand you. If yes, please clarify your answer. Thanks — rose khan, Dec 07 '13 at 22:30

how can I generalize my code to rename column names via asp.net

1 Answers1