Class for sql command in asp.net

Question

2.50/5 (2 votes)

See more:

I want to create class for every sql command in asp.net. I don't want to use stored procedure just create class file for every sql command. How can i do that.
Please help me...Thanks in advance

Posted 13-May-11 19:13pm

rahul dev123

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

IncredibleRam · Answer 1 · 2011-05-13T19:29:00

hello there
what exact thing i got from the question is that you just don't want to create stored procedures and want to pass the queries to the functions created.

but this will not be the exact way this stuff suppose to be done by the way you can do that by creating a class (like data Access layer) where you can put all the classes responsible for all the things like :

create a conClass.cs or you can add a class project to the app you are working on : create a class which will open the connection to the database. now if you want a function which will accept a Query as string and will execute it do it like:

VB

Public Sub ExecutQuery(ByVal qry As String)

        Try
            ConOpen()
            cmd = New SqlCommand(qry, con)
            cmd.ExecuteNonQuery()
            ConClose()

        Catch ex As Exception
            ConClose()
            ErrMsgBox(ex.Message)
        End Try

    End Sub

so now to execute a qury you have to write all the time the following code :
create a instance of this class on the form

dim con as new conClass

and where you want to execute the query write :

con.ExecutQuery("QueryHere")

this code will execute the passed querry what ever it will be (save, update, delete etc.). like this way you can create the other methods like to bind the dropdown list, Datagrid view or any other control.

and to return an array :

VB

Public Function Ret_Array(ByVal qry As String, ByRef values() As Integer)

        Dim i As Integer

        Try
            ConOpen()
            cmd = New SqlCommand(qry, con)

            Dim dr As SqlDataReader = cmd.ExecuteReader

            If dr.HasRows = True And IsDBNull(dr) = False Then
                While dr.Read()
                    If Not IsDBNull(dr.Item(0)) Then
                        ReDim Preserve values(i)
                        values(i) = dr.Item(0)
                        i = i + 1
                    End If

                End While
            Else

            End If
            dr.Close()
            ConClose()

        Catch ex As Exception
            ErrMsgBox(ex.Message)
        End Try

        Return Nothing

    End Function

if this is not what you are expecting as an answer just let me know but if yes then i will provide you the C# code.

Best Regards.

Prasanta_Prince · Answer 2 · 2011-05-13T19:54:00

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

/// <summary>
/// Summary description for MyClass
/// </summary>
public class MyClass
{
    public SqlConnection con;
    public SqlCommand cmd;
    public SqlDataAdapter adp;
    public DataSet ds;
	public MyClass()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public void Connection()
    {
        con = new SqlConnection("Connection String");
        con.Open();
    }
    public DataSet FillDate(string MyString)
    {
        Connection();
        adp = new SqlDataAdapter(MyString,con);
        ds = new DataSet();
        adp.Fill(ds);
        return ds;
    }

}

Hope this can helop you. Here just pass the string from page. and write other function in the class.

IncredibleRam · Answer 3 · 2011-05-13T22:15:00

Dear friend
BTW in the prevoius code (For the first comment) no need to write :

cmd.ExecuteNonQuery();

to know more about this concept click on :
http://www.nigelwhitworth.com/VB/whatisthedifferencebetweenexecutereaderexecutescalarexecutexmlreaderandexecutenonquerydafillobjdt.php[^]
now as in the previous Case you return memberId as a string but here it will select all the data for the Member so here you have to return the dataset or dataredaer instance : do it like

public DataSet SelectMembers(string username)
        {
            SqlConnection con = Database.GetConnection();
            SqlDataAdapter adp = new SqlDataAdapter("select * from members where username=@" + username + "", con);
            DataSet ds = new DataSet();
            adp.Fill(ds);
            return ds;
        }

and on the form or page you want the data
dataset ds = SelectMember("Name of the member");
but here you have to keep one thing in mind that as here you are trying to select the members on the basis of the Username so there may be a possibility that more than two member with the same name exists.

so if you want the data of the member the always use the ID or the field with the primary key for search
best regards.

Class for sql command in asp.net

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0