Click here to Skip to main content
15,895,667 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
SQL StoredProcedure

SQL
CREATE PROCEDURE sp_AddAdmins
@AId		VARCHAR(30),
@AEmail		VARCHAR(50),
@AName		VARCHAR(50),
@APassword	VARCHAR(15),
@AMobile	VARCHAR(15)
AS
BEGIN
IF EXISTS(SELECT AID,AEmail FROM Admins WHERE AID=@AId OR AEmail=@AEmail)
BEGIN
SELECT 2 AS result
END
ELSE
BEGIN
INSERT INTO Admins(AId,AEmail,AName,APassword,AMobile)
VALUES(@AId,@AEmail,@AName,@APassword,@AMobile)
END
END


DataAccess

C#
public int ExecuteNonQuery(string query, CommandType cmdType, SqlParameter[] parms = null)
       {

           int result = 0;
           using (SqlConnection conn = new SqlConnection(connectionString))
           {
               using (SqlCommand cmd = conn.CreateCommand())
               {
                   cmd.CommandText = query;
                   cmd.CommandType = cmdType;
                   if (parms != null)
                   {
                       cmd.Parameters.AddRange(parms);
                   }

                   try
                   {
                       conn.Open();
                       result = cmd.ExecuteNonQuery();
                   }
                   catch
                   {
                       throw;
                   }
               }
           }

           return result;
       }

       public DataTable ExecuteQuery(string query, CommandType cmdType, SqlParameter[] parms = null)
       {
           DataTable dt = new DataTable();
           SqlDataAdapter da;

           using (SqlConnection conn = new SqlConnection(connectionString))
           {
               using (SqlCommand cmd = conn.CreateCommand())
               {
                   cmd.CommandText = query;
                   cmd.CommandType = cmdType;
                   if (parms != null)
                   {
                       cmd.Parameters.AddRange(parms);
                   }

                   try
                   {
                       da = new SqlDataAdapter(cmd);
                       conn.Open();
                       da.Fill(dt);
                   }
                   catch
                   {
                       throw;
                   }
               }
           }

           return dt;
       }
   }


class in Windows Form Application

C#
<pre lang="cs">public int AddAdmins(Admins adm)
{
    int result = 0;
    DataAccess da= new DataAccess();
    string query = "sp_AddAdmins";
    SqlParameter[] param = new SqlParameter[5];
    param[0]=new SqlParameter("@AId",SqlDbType.VarChar);
    param[0].Value = adm.AId;
    param[1] = new SqlParameter("@AEmail", SqlDbType.VarChar);
    param[1].Value = adm.AEmail;
    param[2] = new SqlParameter("@AName", SqlDbType.VarChar);
    param[2].Value = adm.AName;
    param[3] = new SqlParameter("@APassword", SqlDbType.VarChar);
    param[3].Value = adm.APassword;
    param[4] = new SqlParameter("@AMobile", SqlDbType.VarChar);
    param[4].Value = adm.AMobile;


    result=da.ExecuteNonQuery(query, CommandType.StoredProcedure, param);
    return result;
}


Code Behind Button

C#
<pre lang="cs">private void button1_Click(object sender, EventArgs e)
{
    int result = 0;
    Admins adm = new Admins();
    adm.AId = textBox1.Text.Trim();
    adm.AEmail = textBox2.Text.Trim();
    adm.AName = textBox3.Text.Trim();
    adm.APassword = textBox4.Text.Trim();
    adm.AMobile = textBox5.Text.Trim();

    result = adm.AddAdmins(adm);

    if (result == 1)
        MessageBox.Show("Admin Added Successfully");
    else if (result == 2)
        MessageBox.Show("Admin Added Successfully");
    else
        MessageBox.Show("Admin Not Added Successfully");
}
Posted

1 solution

Your SP only returns a result in 1 case so you'll want to modify that.

To get return results from SQL either call ExecuteReader() and then use a DataReader to read through the results. Or just call .ExecuteScalar() which returns the first column, first row, no matter what the results are.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900