Is Correct Way Codding?. [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

I am new Programmer. In my Comp. I am the Only Programmer. So I have many Questions in my Mind.

In my Project i am using Below Code For add.

MySqlConnection connection = new MySqlConnection(MyConString);
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
command.CommandText = "select * from "开发者_如何学JAVA + datatable + " where code='" + textBox1.Text + "'";
connection.Open();
Reader = command.ExecuteReader();
while (Reader.Read())
{
    already = 1;
}
connection.Close();

Is this Correct Way. Or DAL, Stored Procedure, 3 tire Architure Which is most effective. And What standard Code is maintain in Companies.

I am doing Project and Got Solutions Also. But I don't know this way is Correct or Not.

I think Most of you understand my problem?.

Thanks in Advance....

---

How you divide your code into layers is more a matter of taste than an absolute must. You get some advantages by at least separating the database code from the user interface. You would for example easily notice the problem with using the text from a textbox directly in a database query if the database code was separated from the UI.

There are some serious problems with your code:

• You are not disposing all disposable objects, which wastes resources, and might cause errors in the long run.
• The code is wide open for SQL injections, you should use parameterised queries.

Also:

• You are fetching all the data in the table, when you only need to know if there is any data or not.
• Never use select *, only fetch the fields that you are going to use.

Disposing connections and readers aldo close them, so if you use using blocks to dispose the objects, you don't have to close them first:

using (MySqlConnection connection = new MySqlConnection(MyConString)) {
  using (MySqlCommand command = connection.CreateCommand()) {
    command.CommandText = "select count(*) from " + datatable + " where code = @Code";
    command.Parameters.Add("@Code", dbType.VarChar, 50).Value = textBox1.Text;
    connection.Open();
    if ((int)(command.ExecuteScalar()) > 0) {
      already = 1;
    }
  }
}

---

1. Keep your connection string at global scope, so if it changes you don't have to re-write it again in each method interacts with the database.

2. Your command is varnuable to SQL injection attack, you should change that to something like:

   command.CommandText = "select * from @datatable where code=@code";
   command.Parameters.Add(new SqlParameter("datatable", datatable));
   command.Parameters.Add(new SqlParameter("code", textBox1.Text));
   

3. Use using or try/finally to close your connection whenever you done from it and also do that for the datareader, so:

   using (MySqlConnection connection = new MySqlConnection(MyConString))
   {
       //use the connection here
   }
   

   In general use the using statement with all object that implements IDisposable interface, so they will be disposed probably. when you use try/finally or using you are sure that even if something getting wrong, like exception has been thrown your object is disposed.

4. You should keep your database logic separated from the UI. check the pattern of 3 tier architecturee.

---

To ensure that connections are always closed, open the connection inside of a using block. as shown in the following code fragment. Doing so ensures that the connection is automatically closed when the code exits the block.

using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        // Do work here; connection closed on following line.
    }

This is a part of Connection Pooling. And it is one of the good way of doing Sql connection.