sql server store procedure

thanks man but this help much so let me throw more light on it for you

i have two store procedure

SP1:

USE [EMAILDB]
GO
/****** Object: StoredProcedure [dbo].[Sp_InsertUserLoginInfo] Script Date: 05/25/2016 12:48:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Sp_InsertUserLoginInfo]

(
@LoginName varchar(50),
@Password varchar(50),
@Question varchar(100),
@Answer varchar(80)
OUTPUT
)

AS
/* SET NOCOUNT ON */
INSERT INTO tbl_User_LoginAccount
(LoginName, Password, Question, Answer,Active,Date)
VALUES (@LoginName,@Password,@Question,@Answer,1,getdate())
RETURN

PS2:

USE [EMAILDB]
GO
/****** Object: StoredProcedure [dbo].[Sp_Insert_RegistrationInfo] Script Date: 05/25/2016 13:53:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[Sp_Insert_RegistrationInfo]

(
@LoginId varchar(50),
@FirstName varchar(50),
@LastName varchar(50),
@Country varchar(50),
@Email varchar(50),
@Phone varchar(20),
@DOB datetime,
@Gender varchar(10),
@Interest varchar(300)
OUTPUT
)

AS
/* SET NOCOUNT ON */
declare @Date datetime
set @Date=(select convert(varchar(50),getdate(),103))
INSERT INTO tbl_UserFullInformation
(LoginId, FirstName, LastName, Country, Email, Phone, DOB, Gender,Interest,Date)
VALUES (@LoginId,@FirstName,@LastName,@Country,@Email,@Phone,@DOB,@Gender
,@Interest,@Date)
RETURN

and this is the c# code that take the parameters to the SP for insert :

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using SMTPMailClient.DAL;
///

/// Summary description for UserRegistrationBL
///

public class UserRegistrationBL:Connection
{
public static DataSet ds;
public UserRegistrationBL()
{
//
// TODO: Add constructor logic here
//
}
private string _LoginName, _Password, _Question, _Answer,_LoginTime,_LogoutTime;
public string LoginTime
{
get { return _LoginTime; }
set { _LoginTime = value; }
}
public string LogoutTime
{
get { return _LogoutTime; }
set { _LogoutTime = value; }
}
public string LoginName
{
get { return _LoginName; }
set { _LoginName = value; }
}
public string Password
{
get { return _Password; }
set { _Password = value; }
}
public string Question
{
get { return _Question; }
set { _Question = value; }
}
public string Answer
{
get { return _Answer; }
set { _Answer = value; }
}
private string _LoginId, _FirstName, _LastName, _Country, _Email, _Phone, _Gender, _Interest;
public string LoginId
{
get { return _LoginId; }
set { _LoginId = value; }
}
public string FirstName
{
get { return _FirstName; }
set { _FirstName = value; }
}
public string LastName
{
get { return _LastName; }
set { _LastName = value; }
}

public string Country
{
get { return _Country; }
set { _Country = value; }
}
public string Email
{
get { return _Email; }
set { _Email = value; }
}
public string Phone
{
get { return _Phone; }
set { _Phone = value; }
}
private DateTime _DOB,_LoginDate,_LogoutDate,_Date;
public DateTime Date
{
get { return _Date; }
set { _Date = value; }
}
public DateTime DOB
{
get { return _DOB; }
set { _DOB = value; }
}
public DateTime LoginDate
{
get { return _LoginDate; }
set { _LoginDate = value; }
}
public DateTime LogoutDate
{
get { return _LogoutDate; }
set { _LogoutDate = value; }
}
public string Gender
{
get { return _Gender; }
set { _Gender = value; }
}
public string Interest
{
get { return _Interest; }
set { _Interest = value; }
}
public void InsertUserLoginInfo()
{
SqlParameter[] p=new SqlParameter[4];
p[0] = new SqlParameter("@LoginName", this._LoginName);
p[0].DbType=DbType.String;
p[1]=new SqlParameter("@Password",this._Password);
p[1].DbType=DbType.String;
p[2]=new SqlParameter("@Question",this._Question);
p[2].DbType=DbType.String;
p[3]=new SqlParameter("@Answer",this._Answer);
p[3].DbType=DbType.String;
SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "Sp_InsertUserLoginInfo", p);
}
public void InsertRegistrationInfo()
{
SqlParameter[] p = new SqlParameter[10];
p[0] = new SqlParameter("@LoginId", this._LoginName);
p[0].DbType = DbType.String;
p[1] = new SqlParameter("@FirstName", this._FirstName);
p[1].DbType = DbType.String;
p[2] = new SqlParameter("@LastName", this._LastName);
p[2].DbType = DbType.String;
p[3] = new SqlParameter("@Country", this._Country);
p[3].DbType = DbType.String;
p[4] = new SqlParameter("@Email", this._Email);
p[4].DbType = DbType.String;
p[5] = new SqlParameter("@Phone", this._Phone);
p[5].DbType = DbType.String;
p[6] = new SqlParameter("@DOB", this._DOB);
p[6].DbType = DbType.Date;
p[7] = new SqlParameter("@Gender", this._Gender);
p[7].DbType = DbType.String;
p[8] = new SqlParameter("@Interest", this._Interest);
p[8].DbType = DbType.String;
p[9] = new SqlParameter("@Date", this._Date);
p[9].DbType = DbType.Date;
SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "Sp_Insert_RegistrationInfo", p);
}

and when you click the submit button this code takes the user input to the code above for insertion:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;


public partial class Registration_frmUserRegistration : System.Web.UI.Page
{
Country country = new Country();
IntrestBL intrest = new IntrestBL();
UserRegistrationBL registration = new UserRegistrationBL();
protected void Page_Load(object sender, EventArgs e)
{
GMDatePicker1.MaxDate = System.DateTime.Now;
GMDatePicker1.MinDate = System.DateTime.Now.AddYears(-100);
BindRandomNumber();
if (!IsPostBack)
{
BindData();
}
GMDatePicker1.Attributes.Add("readonly", "readonly()");
}

//------------------------------------------------------------------------------
private void BindRandomNumber()
{
Random num = new Random();
}
//---------------------------------------------------------------------------
private void BindData()
{
ddlCountry.DataSource = country.ShowCountry();
ddlCountry.DataTextField = "CountryName";
ddlCountry.DataBind();
ddlCountry.Items.Insert(0, "Choose One...");

chklistInrest.DataSource = intrest.ShowAllIntrest();
chklistInrest.DataTextField = "Interest";
chklistInrest.DataBind();

}


protected void lnkAvailability_Click(object sender, EventArgs e)
{
if (txtName.Text.Trim().Length < 1)
{
lblAvailability.Text = "Plz Enter User Name...!";
txtName.Focus();
return;

}
registration.LoginName = txtName.Text.Trim();
if (registration.CheckUserAvailability() == true)
{
lblAvailability.Text = "User Name Already Exists...!";

}

else
{
lblAvailability.Text = "User Name Not Exists...!";
}
}

protected void btnContinue_Click(object sender, EventArgs e)
{
try
{
registration.LoginName = txtName.Text.Trim();
if (txtPassword.Text.Trim().Length < 6)
{
lblMsg.Focus();
lblMsg.Text = "Password Should Have Atleast 6 Characters";
return;
}
else
{

registration.Password = txtPassword.Text.Trim();
registration.Question = txtQuestion.Text.Trim();
registration.Answer = txtAnswer.Text.Trim();
registration.FirstName = txtFName.Text.Trim();
registration.LastName = txtLName.Text.Trim();

registration.Country = ddlCountry.SelectedItem.Text.Trim();
registration.Email = txtMail.Text.Trim();
registration.Phone = txtPhone.Text.Trim();
registration.DOB = GMDatePicker1.Date;
registration.Gender = ddlGender.SelectedItem.Text.Trim();

string Intrest = "";
for (int i = 0; i < chklistInrest.Items.Count; i++)
{
if (chklistInrest.Items[i].Selected == true)
Intrest = Intrest + chklistInrest.Items[i].Text + ",";


}
Intrest = Intrest.Remove(Intrest.Length - 1, 1);
registration.Interest = Intrest;
registration.Date = System.DateTime.Now.Date;
registration.InsertUserLoginInfo();
registration.InsertRegistrationInfo();

Session["UserName"] = registration.LoginName;
Response.Redirect("~/Registration/frmSignUpSuccessful.aspx");

}

}
catch (Exception ex)
{

lblMsg.Text = "User Name Already Exists...!";

}


}
}

thanks man

Hi,

 

Check the following reference :

 

http://stackoverflow.com/questions/13318924/how-do-i-insert-into-two-tables-all-at-once-in-a-stored-procedure