.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Insert Data using Parameters through Stored Procedure

Posted By:Syed Shakeer Hussain       Posted Date: April 13, 2009    Points: 25    Category: DataBase    URL: http://www.dotnetspark.com  
 

Insert Data using Parameters through Stored Procedure.

First, create a stored procedure that may or may not accept parameters, and then call the stored procedure from within the code supply the necessary values if parameters are needed. The following example illustrates how to insert a new user in a users table that has a username and password field.

CREATE PROCEDURE [dbo].[InsertUser] (
    @Username varchar(50),
    @Password varchar(50)
) AS
INSERT INTO Users VALUES(@Username, @Password)

Note the DataType  of Varchar(50) should be same as we created in a table for a column username and password

string username = textusername.Text

string password =txtpassword.Text

SqlConnection conn = new SqlConnection("Data Source=localhost;Database=MyDB;Integrated Security=SSPI"); SqlCommand command = new SqlCommand("InsertUser", conn); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@Username", SqlDbType.VarChar).Value = username; command.Parameters.Add("@Password", SqlDbType.VarChar).Value = password; conn.Open(); int rows = command.ExecuteNonQuery(); conn.Close();

First, we retrieve the username and password information from the user. This information may be entered onto a form, through a message dialog or through some other method. The point is, the user specifies the username and password and the applicaton inserts the data into the database. Also notice that we called the ExecuteNonQuery() method of the Connection object. We call this method to indicate that the stored procedure does not return results for a query but rather an integer indicating how many rows were affected by the executed statement. ExecuteNonQuery() is used for DML statements such as INSERT, UPDATE and DELETE. Note that we can test the value of rows to check if the stored procedure inserted the data successfully.

 Subscribe to Articles

     

Further Readings:

Responses
Author: Athira Sreejith         Company URL: http://www.dotnetspark.com
Posted Date: April 16, 2009

Hi..

Like this also we can try

DemoHelper objAdd = new DemoHelper();
string Success = string.Empty;
Success = objAdd.CreateUser(txtUsername.Text, txtPassword.Text, ddlUserType.SelectedValue, txtFname.Text, txtLname.Text, txtEmail.Text, null, chkStatus.Checked, sb.ToString());

DemoHelper.csPage:
public string CreateUser(string strUname, string strPwd, string strUtype, string strFname, string strLname, string strEmail,string strCreatedBy, bool IsActive,string Description)
{
SQLHelper objHelp = new SQLHelper();
bool IsSuccess = false;
SqlParameter p1 = new SqlParameter("@UserName", strUname);
SqlParameter p2 = new SqlParameter("@Password", strPwd);
SqlParameter p3 = new SqlParameter("@UserType", strUtype);
SqlParameter p4 = new SqlParameter("@FirstName", strFname);
SqlParameter p5 = new SqlParameter("@Lastname", strLname);
SqlParameter p6 = new SqlParameter("@Email", strEmail);
SqlParameter p7 = new SqlParameter("@CreatedBy", strCreatedBy);
SqlParameter p8 = new SqlParameter("@IsActive", IsActive);
SqlParameter p9 = new SqlParameter("@Description", Description);
IsSuccess = objHelp.ExecuteNonQuerySPDB("UserRegistration", new SqlParameter[] { p1, p2, p3, p4, p5, p6, p7, p8, p9 });
if (IsSuccess)
{
strSuccess = "Inserted successfully";
}
else
{
strSuccess = "Insertion failed";
}
return strSuccess;
}

SQLHelper.csPage:
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public class SQLHelper
{
private string strConnectionString = string.Empty;

public SQLHelper()
{
// strConnectionString = ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString;
}

#region Common Function definition to execute any query

/// <summary>
/// Create the connection with the DataBase using web.config Appsettings tag.
/// </summary>
/// <returns> sqlconnection object</returns>
public SqlConnection connection()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString);
return con;
}

/// <summary>
/// Used to execute query using ExecuteNonQuery() Method
/// </summary>
/// <param name="Query"></param>
/// <returns>Boolean</returns>
public bool ExecuteNonQueryDB(string Query)
{
if (Query != "")
{
SqlConnection m_Scon = connection();
SqlCommand m_Scmd = new SqlCommand(Query, m_Scon);
m_Scon.Close();
m_Scon.Open();
int m_Sdr = m_Scmd.ExecuteNonQuery();
if (m_Sdr > 0)
{
if (m_Scon.State == ConnectionState.Open)
{
m_Scon.Close();
}
return true;
}
else
{
if (m_Scon.State == ConnectionState.Open)
{
m_Scon.Close();
}
return false;
}
}
else
{
return false;
}
}

/// <summary>
/// Used to execute query using ExecuteReader() Method
/// </summary>
/// <param name="Query"></param>
/// <returns>IDataReader</returns>
public SqlDataReader ExecuteReaderDB(string Query)
{
SqlConnection m_Scon = connection();
SqlCommand m_Scmd = new SqlCommand(Query, m_Scon);
m_Scon.Close();
m_Scon.Open();
SqlDataReader m_Sdr = m_Scmd.ExecuteReader(CommandBehavior.CloseConnection);
return m_Sdr;
}

/// <summary>
/// Used to execute query using ExecuteDataSet() Method
/// </summary>
/// <param name="Query"></param>
/// <returns>DataSet</returns>
public DataSet ExecuteDataSetDB(string Query)
{
if (Query != "")
{
SqlConnection m_Scon = connection();
SqlCommand m_Scmd = new SqlCommand(Query, m_Scon);
DataSet m_DSObj = new DataSet();

SqlDataAdapter adr = new SqlDataAdapter(m_Scmd);
adr.Fill(m_DSObj);

if (m_Scon.State == ConnectionState.Open)
{
m_Scon.Close();
}

return m_DSObj;
}
else
{
return null;
}
}

///<summary>
///Used to execute the stored procedure and return DataSet
///</summary>
///<param name="ProcedureName"></param>
///<param name="Parameters"></param>
///<returns>DataSet</returns>
public DataSet ExecuteDataSetSPDB(string ProcedureName, SqlParameter[] Parameters)
{
if (ProcedureName != "")
{
SqlConnection m_Scon = connection();
SqlCommand m_Scmd = new SqlCommand(ProcedureName, m_Scon);
m_Scmd.CommandType = CommandType.StoredProcedure;
DataSet m_DSObj = new DataSet();
if (Parameters != null)
{
foreach (SqlParameter para in Parameters)
{
m_Scmd.Parameters.Add(para);
}
}
SqlDataAdapter adr = new SqlDataAdapter(m_Scmd);
adr.Fill(m_DSObj);

if (m_Scon.State == ConnectionState.Open)
{
m_Scon.Close();
}

return m_DSObj;
}
else
{
return null;
}
}

/// <summary>
/// Used to execute the stored procedure and return IDataReader
/// </summary>
/// <param name="ProcedureName"></param>
/// <param name="Parameters"></param>
/// <returns>IDataReader</returns>
public SqlDataReader ExecuteReaderSPDB(string ProcedureName, SqlParameter[] Parameters)
{
if (ProcedureName != "")
{
SqlConnection m_Scon = connection();
SqlCommand m_Scmd = new SqlCommand(ProcedureName, m_Scon);
m_Scmd.CommandType = CommandType.StoredProcedure;
m_Scon.Close();
m_Scon.Open();
if (Parameters != null)
{
foreach (SqlParameter para in Parameters)
{
m_Scmd.Parameters.Add(para);
}
}
SqlDataReader m_Sdr = m_Scmd.ExecuteReader(CommandBehavior.CloseConnection);


return m_Sdr;
}
else
{
return null;
}
}

/// <summary>
/// Used to execute the stored procedure using executenonquery
/// </summary>
/// <param name="ProcedureName"></param>
/// <param name="Parameters"></param>
/// <returns>Boolean</returns>
public bool ExecuteNonQuerySPDB(string ProcedureName, SqlParameter[] Parameters)
{
if (ProcedureName != "")
{
SqlConnection m_Scon = connection();
SqlCommand m_Scmd = new SqlCommand(ProcedureName, m_Scon);
m_Scmd.CommandType = CommandType.StoredProcedure;
m_Scon.Close();
m_Scon.Open();
if (Parameters != null)
{
foreach (SqlParameter para in Parameters)
{
m_Scmd.Parameters.Add(para);
}
}
int rowsAffected = m_Scmd.ExecuteNonQuery();

if (rowsAffected > 0)
{
return true;
}
else
{
return false;
}
}
else
{
return false;
}
}

/// <summary>
/// Used to execute stored procedure using executescalar method
/// </summary>
/// <param name="ProcedureName"></param>
/// <param name="Parameters"></param>
/// <returns>string</returns>
public string ExecuteScalarSPDB(string ProcedureName, SqlParameter[] Parameters)
{
if (ProcedureName != "")
{
SqlConnection m_Scon = connection();
SqlCommand m_Scmd = new SqlCommand(ProcedureName, m_Scon);
m_Scon.Close();
m_Scon.Open();
if (Parameters != null)
{
foreach (SqlParameter para in Parameters)
{
m_Scmd.Parameters.Add(para);
}
}
object rowsID = m_Scmd.ExecuteScalar();

return rowsID.ToString();
}
else
{
return "";
}
}

#endregion
}

SP:

ALTER PROCEDURE UserRegistration
(@UserName varchar(100)='',
@Password varchar(100)='',
@UserType varchar(100)='',
@FirstName varchar(100)='',
@Lastname varchar(100)='',
@Email varchar(100)='',
@CreatedBy varchar(100)='',
@IsActive bit,
@Description varchar(250)='')

AS
insert into tbl_User(UserName,Password,UserType,FirstName,LastName,Email,CreatedBy,IsActive,Description) values(@UserName,@Password,@UserType ,@FirstName,@Lastname,@Email,@CreatedBy,@IsActive,@Description)
Author: Harry         Company URL: http://www.dotnetspark.com
Posted Date: October 31, 2010

Hi,
this is my code... all are nvarchar(10) datatype,

Dim Comm As SqlCommand = New SqlCommand("c_reg", con1)
Comm.CommandType = CommandType.StoredProcedure
Dim cname As String = tb_cname.Text
Dim cpname As String = tb_pname.Text
Dim ceid As String = tb_ceid.Text
Dim cuname As String = tb_cuname.Text
Dim cpwd As String = tb_cpwd.Text
Comm.Parameters.Add("@cname", SqlDbType.NVarChar, 10).Value = cname
Comm.Parameters.Add("@cperson", SqlDbType.NVarChar, 10).Value = cpname
Comm.Parameters.Add("@cemail", SqlDbType.NVarChar, 10).Value = ceid
Comm.Parameters.Add("@cuid", SqlDbType.NVarChar, 10).Value = cuname
Comm.Parameters.Add("@cpwd", SqlDbType.NVarChar, 10).Value = cpwd
Dim i As Integer = Comm.ExecuteNonQuery()
con.close()

I m able to insert only the first character only to database.ie. if i enter Madhu--only m is inserted. can any1 help me out to solve this?

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend