ASP.NET C# & Stored Procedures

Question

I am making the transition from DataTableAdapters in MSVS to creating Stored Procedures in MS SQL Server.

This is what I have so far.

protected void Submit_Click(object sender, EventArgs e)
    {
         var conString = ConfigurationManager.AppSettings["ConnectionString"].ToString(); 
         using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlCommand cmd = new SqlCommand("getAdministrators", con))
            {
                cmd.Parameters.Add("@userName", SqlDbType.VarChar).Value = userNameTB.Text;
                cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = passwordTB.Text;
                cmd.CommandType = CommandType.StoredProcedure;
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                }
            }
        }
    }

I want to grab all the rows/columns and store them in a DataSet and use the values returned at my will for whatever I wish. What am I missing to accomplish this. Or if someone has an article that could help as well?

I updated the code to use using.

I think the code is slowly getting there...

Note: you should probably get into the habit of using using for things like SqlCommand, SqlConnection, etc — Marc Gravell
– Marc Gravell, Commented Nov 18, 2010 at 3:10
definetely. "open late, close early". also - are you sure those fields are VarChar and not NVarChar? — RPM1984
– RPM1984, Commented Nov 18, 2010 at 3:13
Right in the beginning of the On_Click I throw a using in there? Any code snippets or articles? — balexander
– balexander, Commented Nov 18, 2010 at 3:15
just a suggestion, but it is very confusing when you keep editing you original answer based on the feedback you get...makes the flow of the questions and answers very difficult to follow/understand...maybe post a reply instead so someone down the road reading the question can make sense of the answers and comments that were posted? When you finally are done, your code snippet will work and nobody will understand why anyone was making suggestions to change it... — E.J. Brennan
– E.J. Brennan, Commented Nov 18, 2010 at 15:04

E.J. Brennan · Accepted Answer · 2010-11-18 04:37:23Z

2

This snippet works:

    public static DataSet GetAll(int id)
    {
        using (SqlConnection con = new SqlConnection(Database.ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand("sp_ContactGetAll", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet dsData = new DataSet();
                    da.Fill(dsData);
                    return dsData;
                }
            }
        }
    }

answered Nov 18, 2010 at 4:37

E.J. Brennan

46.9k8 gold badges94 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

balexander Over a year ago

I have the code firing when a button is clicked though, so what should I replace RETURN with?

E.J. Brennan Over a year ago

don't return anything, change my function declaration to your submit_click event declaration and drop the return statement.

Marc Gravell · Accepted Answer · 2010-11-18 03:09:48Z

0

I may be wrong, but I would expect the following to work:

using(var reader = cmd.ExecuteReader()) {
  ds.Tables[0].Load(reader);
}

(assuming you want to populate the zeroth table)

answered Nov 18, 2010 at 3:09

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

3 Comments

balexander Over a year ago

I want to return everything into a nice organized set and use the least amount of code to reference it.

Marc Gravell Over a year ago

@Bry4n a fine aim - I don't understand that that means in terms of my reply, though.

balexander Over a year ago

I don't know. I probably don't know the proper question to ask. I never worked with SPs in C# I mainly use ColdFusion at work which is very different. Like do i use DataSets and a ExecuteReader? Which needs to be in or out of the connection? ha

Raymund · Accepted Answer · 2010-11-18 03:33:13Z

0

http://msdn.microsoft.com/en-us/library/9kcbe65k.aspx

I guess you missed cmd.ExecuteReader

answered Nov 18, 2010 at 3:33

Raymund

7,9125 gold badges50 silver badges80 bronze badges

Collectives™ on Stack Overflow

ASP.NET C# & Stored Procedures

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related