SQL Server Procedure and a Record set

Question

I want to execute a stored procedure in SQL Server, then look at the results to see whether any have returned. Here is my c#:

public ActionResult LogOn(LogOnModel model, string returnUrl)
{
        SqlConnection conn = null;
        conn = new SqlConnection("Server=(local);Database=holidaydatabase;User Id=holidaylogin;Password=test");

        SqlCommand command = new SqlCommand("EmployeeValidation", conn);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add("@Username", SqlDbType.VarChar).Value = model.UserName;
        command.Parameters.Add("@Password", SqlDbType.VarBinary).Value =model.Password;

        conn.Open();
        command.ExecuteNonQuery();

        conn.Close();
}

This is the SQL Server stored procedure:

DECLARE @EmployeeID int,
    @LinemanagerID int,
    @EmployeeJobRoleID int

SELECT  @EmployeeID = E.ID,
    @LinemanagerID = E.LineManagerID,
    @EmployeeJobRoleID = E.Job_RoleID
FROM    Employees E
WHERE   E.Username = @Username and
    E.Password = @Password

SELECT  E.First_name + ' ' + E.Second_Name as Full_name,
    J.Name,
    E.Username,
    J.MaxUsersOffPerDay,
    E.ID,
    E.LineManagerEligibility,
    E.Date_Joind_Company,
    J.Name,
    J.MaxUsersOffPerDay
FROM    Employees E
    INNER JOIN JobRole J ON E.Job_RoleID = J.ID 
WHERE   Username = @Username
AND [Password] = @Password

SELECT  E.Username
FROM    Employees E
WHERE   E.ID = @LinemanagerID

SELECT  [Values]
FROM    HolidayValues        

SELECT  E.First_name + ' ' + E.Second_Name
FROM    Employees E
WHERE   E.ID = @LinemanagerID

How can I look through these selects and assign variables to the results?

Please see the [SO post](http://stackoverflow.com/questions/886293/how-do-i-execute-a-stored-procedure-once-for-each-row-returned-by-query). You basicly need a cursor to fetch. — Oğuz Sezer, Jul 18 '14 at 14:24
@OğuzSezer, in my experience, using a cursor in SQL is almost always the wrong way to do it. In this case the OP is using `.ExecuteNonQuery()`, but is actually trying to query, so they should use the `Fill` method. — Nathan Koop, Jul 18 '14 at 14:45

T.S. · Answer 1 · 2014-07-18T16:02:30.210

1

Instead of using command.ExecuteNonQuery(); use SqlDataAdapter.Fill to fill a dataset. You should have all your selects populate DataTables

 var ds = new Dataset();
 var da = new SqlDataAdapter(command);
 da.Fill(ds); // your selects should now be in ds.Tables[0], ds.Tables[1], etc

Later in the code. And this is just one of many concepts here

private int _index = -1;

private void buttonNext_Click(.....)
{
    var dt = ds.Tables[0];
    if (dt.RowCount - 1 > _index)
    {
        _index++;
        if (dt.rows[_index]["Name"] != DBNull.Value)
            txtName.text = dt.rows[_index]["Name"].ToString();

        if (dt.rows[_index]["UserName"] != DBNull.Value)
            txUserName.text = dt.rows[_index]["UserName"].ToString();
    }
}

Or you can just bind your table to your grid

myGrid.DataSource = ds.Tables[0];

Done

edited Jul 18 '14 at 16:02

answered Jul 18 '14 at 14:33

T.S.

18,195
11
58
78

But how do I loop or access the results in the da.tables[0] ?? – user3726773 Jul 18 '14 at 15:14
@user3726773 Each `datatable` in `dataset` will have columns with names of your columns from select, e.g. `name`, `username`. You can bind it to grid. YOu can read it to textboxes. For example `txt.text=da.Tables[0].rows[0][0].ToString()` - pending that you have any rows in the table and value is not `dbNull`, this will show value of the first column of the first row in the text box. – T.S. Jul 18 '14 at 15:49
@user3726773 I updated my answer with 2 of the concepts – T.S. Jul 18 '14 at 16:02

score 1 · Answer 2 · answered Jul 18 '14 at 14:33

1

The SqlDataAdapter Fill() method can populate a DataSet type.

http://msdn.microsoft.com/en-us/library/zxkb3c3d(v=vs.110).aspx

This allows you to iterate through the data set tables to see what is and is not populated.

answered Jul 18 '14 at 14:33

Nick Vaccaro

5,428
6
38
60

score 0 · Answer 3 · answered Jul 18 '14 at 14:51

Apart from SQLAdapter, you can use SQLDataReader too.

You can select the values from the SQL proc and bind the values in the custom object you have. For example, if you have,

 public class EmployeeDetails
    {
        public string FirstName{ get; set; }
        public string LastName{ get; set; }
//You can add more property as per your requirement
    }
 List<EmployeeDetails> emp = new List<EmployeeDetails>();
using (SqlConnection Conn = new SqlConnection(ConnectionString))//ConnectionString from your config file
                {
                    Conn.Open();
                    using (SqlCommand Cmd = Conn.CreateCommand())
                    {
                        Cmd.CommandText = "dbo.your_proc_name";
                        Cmd.CommandType = CommandType.StoredProcedure;

                        using (SqlDataReader dr = Cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                emp.Add(new EmployeeDetails{ FirstName= dr[0].ToString(), LastName= dr[1].ToString()});
                            }
                            dr.Close();
                        }
                    }
                }

Do you think it is difficult to make it one select? – Ranajit Chatterjee Jul 18 '14 at 16:35 — Ranajit Chatterjee, Jul 18 '14 at 16:35

MarceloRB · Answer 4 · 2014-07-18T15:59:06.267

I don't know if I understand exactly what you need. But if you what to know if you have results in your C# code, then you should use a DataSet which can be filled with many tables. Then your code could be:

public ActionResult LogOn(LogOnModel model, string returnUrl)
{
    SqlConnection conn = null;
    conn = new SqlConnection("Server=(local);Database=holidaydatabase;User Id=holidaylogin;Password=test");

    SqlCommand command = new SqlCommand("EmployeeValidation", conn);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add("@Username", SqlDbType.VarChar).Value = model.UserName;
    command.Parameters.Add("@Password", SqlDbType.VarBinary).Value =model.Password;

    DataSet ds = new DataSet();
    SqlDataAdapter dataadapter = new SqlDataAdapter(command, conn);
    dataadapter.Fill(ds);

    ds.Tables(0).Rows.Count //to know if the query returned values. 
    ds.Tables(1).Rows.Count 
    ds.Tables(2).Rows.Count

}

You can Iterate through the rows of each table and get the values

string sValue = "";
foreach(DataTable dt in ds.Tables)
{
    foreach(DataRow dr in dt.Rows)
    {
        sValue = dr[0].ToString();
        // Here you could iterate through the columns collection 
        //foreach(DataColumn dc in dr.dt.Columns)
        //{
        //  sValue = dr(dc.ColumnName).ToString();
        //}
    }
}

Or you can access directly to any column in any row for each table

sValue = ds.Tables(0).Rows(0)("Full_name").ToString();

This will throw an exeption if Tables(0) doesn't have any row.

i want to look in to the rows and check the values of the selects — user3726773, Jul 18 '14 at 15:37
You can Iterate through the rows of each table and get the values string sValue = ""; foreach(DataTable dt in ds.Tables) { foreach(DataRow dr in dt.Rows) { sValue = dr(0).ToString(); // Here you could iterate through the columns collection //foreach(DataColumn dc in dr.dt.Columns) //{ // sValue = dr(dc.ColumnName).ToString(); //} } } Or you can access directly to any column in any row for each table sValue = ds.Tables(0).Rows(0)("Full_name").ToString(); This will throw an exeption if Tables(0) doesn't have any row. — MarceloRB, Jul 18 '14 at 15:50
I've edited the first answer. I can't format code in the comment area. — MarceloRB, Jul 18 '14 at 16:00

SQL Server Procedure and a Record set

4 Answers4