Creating a Class for SqlConnection in ASP.NET and then use in your application?

Question

I'm having to select, update, delete, etc. basically everywhere on my web application and every time I'm having to write something like this:

con.Open();
cmd = new SqlCommand("update items set item_cost = @cost, item_retail_value = @retail, item_v_style = @v_style, item_v_color = @v_color, item_description = @description, " +
                    "item_date_modify = @date, item_time_modify = @time, item_user_modify = @user where item_style = @style and item_color = @color and item_sec_dimenssion = @sec", con);

cmd.Parameters.Add("@style", SqlDbType.VarChar, 15).Value = styl;
cmd.Parameters.Add("@color", SqlDbType.VarChar, 3).Value = colr;
cmd.Parameters.Add("@sec", SqlDbType.VarChar, 8).Value = sdim;
cmd.Parameters.Add("@size", SqlDbType.VarChar, 3).Value = size;
cmd.Parameters.Add("@cost", SqlDbType.VarChar, 8).Value = sprice;
cmd.Parameters.Add("@retail", SqlDbType.VarChar, 8).Value = sretail;
cmd.Parameters.Add("@uom", SqlDbType.VarChar, 3).Value = uom;
cmd.Parameters.Add("@sku", SqlDbType.VarChar, 10).Value = sku;
cmd.Parameters.Add("@barcode", SqlDbType.VarChar, 20).Value = barcode;
cmd.Parameters.Add("@v_style", SqlDbType.VarChar, 100).Value = v_style;
cmd.Parameters.Add("@v_color", SqlDbType.VarChar, 20).Value = v_color;
cmd.Parameters.Add("@description", SqlDbType.VarChar, 40).Value = description;
cmd.Parameters.Add("@date", SqlDbType.VarChar, 20).Value = date;
cmd.Parameters.Add("@time", SqlDbType.VarChar, 20).Value = time;
cmd.Parameters.Add("@user", SqlDbType.VarChar, 20).Value = user;
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();

Is there a way to create a Class to create SqlConnection with a SqlCommand for "Select", "Update", "Delete", etc. and just provide the table, the fields, parameters and the criteria so I don't have to write all this code every time.

Any help will really appreciate it.

What about using LINQ, Entity Framework or any other ORM out there? — Nikola Anusev, Jul 29 '12 at 20:41
Can you be a little more descriptive? I'm kind of new to ASP.NET — jorame, Jul 29 '12 at 20:45
I could, but it would be better if you tried googling these terms or searching them here on SO. That way, you will gain much more information much faster. In general, this has nothing to do with ASP.NET or web-programming. — Nikola Anusev, Jul 29 '12 at 20:48
@jorame Show some effort and research any of the those term. — paparazzo, Jul 29 '12 at 20:55
Working on it. I just wasn't sure what Nikola was talking about... — jorame, Jul 29 '12 at 20:56

score 1 · Answer 1 · answered Jul 29 '12 at 20:41

1

Linq2Sql
Entity Framework
nHibernate
Dapper
BLToolkit

answered Jul 29 '12 at 20:41

Sergei Rogovtcev

5,804
2
22
35

Can you be a little more descriptive? I'm kind of new to ASP.NET – jorame Jul 29 '12 at 20:45
That has nothing to do with asp.net. Have you tried googling any of these terms? – Sergei Rogovtcev Jul 29 '12 at 21:03

score -1 · Answer 2 · answered Jul 29 '12 at 20:42

-1

create a class with Singleton and then every time you want to do anything with SqlConnection you can use the same object.

answered Jul 29 '12 at 20:42

Embedd_0913

16,125
37
97
135

2

Making a singleton of SqlConnection is usually a very bad idea. You should open and close it as soon as possible. – Sergei Rogovtcev Jul 29 '12 at 21:05
@jorame I thought you want to have some kind of wrapper of SqlConnection , that's why I suggested to create a singleton and wrap connection in that class and create other methods to do the work. – Embedd_0913 Jul 29 '12 at 21:09
I want a way so I don't have to open, close, create a command for every select, update or delete statement I have to run against the DB – jorame Jul 29 '12 at 21:11
He doesn't care about connection management. He cares about reusing code for common queries. – usr Jul 29 '12 at 22:39
Exactly, I want a way to re-use code. Would you be able to provide an example on how to accomplish this?? – jorame Jul 29 '12 at 22:43

Creating a Class for SqlConnection in ASP.NET and then use in your application?

2 Answers2