8 replies to this topic

[Hamed]

Hello,
I want to do a C# SQL class that help me to don't use Sqlconnections and ... many times in my programs
Now I have below code:
I have problem with data reader and adding parameters:
Help me to do it:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;

/// <summary>
/// Summary description for mssql
/// Here I code a class to do my MS SQL jobs 
/// With This class I don't need to use hard coding for each Query!
/// </summary>
public class mssql
{
    public SqlConnection SQLConnection;
    public SqlCommand SQLCommand;
    //Do a constructor with open connection (Input Connection String)
    public mssql(string cstr)
    {
        SQLConnection = new SqlConnection(cstr);
        SQLConnection.Open();
    }

    //Make easy adding new paramter to str
    public void addParam()
    {

    }

    //exQuery just run Query and return last record ID
    public void exQuery(string str)
    {
        SQLCommand = new SqlCommand(str, SQLConnection);
        SQLCommand.ExecuteNonQuery();
    }

    //Data Reader to read str as Query
    public 
    ~mssql()
    {
        SQLConnection.Close();
    }

}

In exQuery I need to add parameter I want to add parameters just by calling exQuery(Query,Array of params name,Array of Values)

for data reader I want to give a query and output as array!

[Momerath]

One thing:

    public 

    ~mssql()

    {

        SQLConnection.Close();

    }

Don't implement destructors in C# as they are not always called. Your class should inherit the IDisposable interface and you should handle any cleanup in the Dispose() method.

As for the rest, what problems are you having? It seems like a straight forward iteration though the arrays to add the parameters and values.

[sam_coder]

the first question, to add a parameters
you use

command.Parameters.AddWithValue("@parmname", value); //this will automatically infer the data type and escape values to prevent injection attacks

parameters are added to the command, not the data reader.

You create the data reader like this:

IDataReader reader = command.ExecuteDataReader(); 

//since you've already created the command with parameters, and context to the connection, its all ready to read now.


while (reader.Read()) {

     //You can now read values into an object array, or read by type.

     reader.GetValues(object_array_thats_long_enough);

    //or you can read specific values

    int int_value = reader.GetInt32(0);

    string string_value = reader.GetString(1);

}

On to the next part.
it shouldn't matter. You do NOT want to create and open a single SQL Connection with the class, and then try using it that way. This works differently than ADODB.

ADO.NET will automatically pool connections for you behind the scenes, so you always want to have a pattern like this:

using (SqlConnection connection = connectionFactory.CreateConnection()) { //assuming you have a factory helper class

     connection.Open();

     using (SqlCommand command = new SqlCommand(connection, query)) {

          command.Parameters.AddWithValue("@parmname", value);

          int records_affected = command.ExecuteNonQuery();

     }

}

When the end of the using block is hit, dispose will automatically be called on it. This would be true for connections, commands, data readers etc.
so as you can see, the close never really needs to be called.dispose will handle it.

Now even though you call these many times over, your not creating really any additional overhead, since the close function in ADO.NET will actually leave the connection open for a time, and just release it to a pool so it can be used again.

The way the pool works is to automatically fully close them after they havent been used in a while. So this just makes your app instantly scalable to a certain extent. you can configure the pool size if you need to, but it would be very unlikely.

hope that helps

[Tonchi]

this is so great idea...write a documentation and post it here when you finish

[Hamed]

Yes, it is great idea but now I don't have time to do it maybe after final exams I did it!
How can I use class in C#??

[Tonchi]

if your class is static then you can use it like this:

static class hello

{

  static void Hello()

   {

     Console.WriteLine("Hello, World!");

   }

}


class Program

{

  static void Main()

  {

    hello.Hello();

   }

}

if it is non static then you have to create instance of hello class like this:

class hello

{

  void Hello()

  {

    Console.WriteLine("Hello, World!");

   }

}

class Program

{

  static void Main()

  {

    hello hello2 = new hello();

    hello2.Hello();

   }

}

[sam_coder]

I've implemented something like this as static before, and it's nice because it's easy.
But be careful with that singleton way of thinking. It can also make you're app much less scalable.

[Tonchi]

it would be nice to make all methods for mssql so people can easy work with it

[sam_coder]

I agree with making re-usable blocks of code.

but just remember, this type of convenience class is not going to be for everyone, and would essentially still required business classes to wrap them.

Ultimately, the extra layer of 'convenience' is going to make you're code less efficient.

conceptually, it's much like having someone to tell someone to move a pallet jack.

My personal feeling on this is that it's a fantastic way for you to get your head around how ADO.NET works. And maybe use it for a few projects, but I would hope that it would allow you to leverage some new knowledge to then stop using it once you have a better understanding of how this stuff works.

There are some bits of repeatable logic however, that I think belong in utility functions, like for example, creating connections.

since you know that every time you execute something against the database you will have to create a connection, and in the same way, it does make a lot of sense to build some sort of connection factory to do this for you. It can make management a lot easier. But by forcing how you interact with the connection into additional utility functions, it can really make it cumbersome.

example:

in my opinion, the right way

public string getName(int id) {

using (SqlConnction connection = cf.createConnection()) {

   using (SqlCommand cmd = new SqlCommand(cf, "SELECT name FROM poeple WHERE id = @id")) {

      cmd.Parameters.AddWithValue("@id", id);

      return (string)cmd.ExecuteScalar();

   }

}

}

in my opinion, the wrong way

public string getName(int id) {

   return (string)SQL.GetValue(new SqlConnection("connectionString"), "SELECT name FROM poeple WHERE id = @id", new SqlParameter[] { new SqlParameter() { Name = "@id", Value = id, Type = typeof(int) } });

}

I mean this is all pseudo code, but you can see, one is verbose, more effecient without the generic code wrapping it, and more than likely easier to manage.

The second has some advantages, by forcing you to always do something in the same way, but sooner or later you'll run into the type of situation where you have to break away from it. Also if SQL ended up being static, youd more than likely run into other issues too.