recall of sql connectivity in c# asp .net with detailed example

Recall of sql connectivity in c# asp .net with detailed example

Hello friend, now I am giving you a example in which we use basic controls with database connectivity. We also use store procedure and configurationamanager to access connectionstring defined (<connectionstrings>) to manipulate data with server.

We used a simple form which use basic controls like textbox, radiobutton, label, dropdownlist, button etc.

See some coding samples below :

<body>
<form id=”form1″ runat=”server”>
<div>
<table>
<tr>
<td>
First Name:
</td>
<td>
<asp:TextBox ID=”txtFirstName” Placeholder=”First Name” runat=”server”></asp:TextBox>
</td>
</tr>
<tr>
<td>
Last Name:
</td>
<td>
<asp:TextBox ID=”txtLastName” Placeholder=”Last Name” runat=”server”></asp:TextBox>
</td>
</tr>
<tr>
<td>
Address:
</td>
<td>
<asp:TextBox ID=”txtAddress” placeholder=”Address” TextMode=”MultiLine” runat=”server”></asp:TextBox>
</td>
</tr>
<tr>
<td>
Gender:
</td>
<td>
<asp:RadioButton ID=”lbtnMale” GroupName=”Gender” Text=”Male” runat=”server” />
<asp:RadioButton ID=”lbtnFemale” GroupName=”Gender” Text=”Femal” runat=”server” />
</td>
</tr>
<tr>
<td>
Contact:
</td>
<td>
<asp:TextBox ID=”txtContact” Placeholder=”Contact no” runat=”server”></asp:TextBox>
</td>
</tr>
<tr>
<td>
Email:
</td>
<td>
<asp:TextBox ID=”txtEmail” Placeholder=”Email” runat=”server”></asp:TextBox>
</td>
</tr>
<tr>
<td>
Occupation:
</td>
<td>
<asp:DropDownList ID=”ddlOccupation” Width=”173px” runat=”server”>
<asp:ListItem Text=”Select” Value=”0″></asp:ListItem>
<asp:ListItem Text=”Student” Value=”1″></asp:ListItem>
<asp:ListItem Text=”Doctor” Value=”2″></asp:ListItem>
<asp:ListItem Text=”Teacher” Value=”3″></asp:ListItem>
<asp:ListItem Text=”Other” Value=”4″></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
Date of Birth:
</td>
<td>
<asp:TextBox ID=”txtDateOfBirth” Placeholder=”Date of Birth” runat=”server”></asp:TextBox>
</td>
</tr>
<tr>
<td>
User ID:
</td>
<td>
<asp:TextBox ID=”txtUserID” Placeholder=”User ID” runat=”server”></asp:TextBox>
</td>
</tr>
<tr>
<td>
Password:
</td>
<td>
<asp:TextBox ID=”txtPassword” TextMode=”Password” Placeholder=”Password” runat=”server”></asp:TextBox>
</td>
</tr>
<tr>
<td colspan=”2″ align=”center”>
<asp:Button ID=”btnLogin” runat=”server” Text=”Save”
onclick=”btnLogin_Click” />
<asp:Button ID=”btnReset” runat=”server” Text=”Reset”
onclick=”btnReset_Click” />
</td>
</tr>
<tr>
<td>
<asp:Label ID=”lblMessage” runat=”server” Text=””></asp:Label>
</td>
</tr>
</table>
</div>
</form>

Here is the c# coding:

protected void btnLogin_Click(object sender, EventArgs e)
{
string FirstName = txtFirstName.Text;
string LastName = txtLastName.Text;
string Address = txtAddress.Text;
string UserID = txtUserID.Text;
string Password = txtPassword.Text;
string Email = txtEmail.Text;
string Contact = txtContact.Text;
string Occupation = “”;
if (ddlOccupation.SelectedIndex > 0)
{
Occupation = ddlOccupation.SelectedItem.Text.ToString();
}
else
{
lblMessage.Text = “Please choose your occupation”;
}
string Gender = “”;
if (lbtnMale.Checked == true)
{
Gender = “Male”;
}
else if (lbtnFemale.Checked == true)
{
Gender = “Female”;
}
else
{
lblMessage.Text = “Please choose your gender”;
}
DateTime DateOfBirth = DateTime.Now.Date;
try
{
DateOfBirth = Convert.ToDateTime(txtDateOfBirth.Text);
}
catch (Exception ex)
{
lblMessage.Text = “Invalid date”;
}
int n = ClassUser.SaveUser(FirstName, LastName, Address, Gender, Contact, Email, Occupation, DateOfBirth, UserID, Password);
if (n == 1)
{

clear();
lblMessage.Text = “Data save successfully”;
Response.Redirect(“BookingApply.aspx”);
}
else if (n == -1)
{
lblMessage.Text = “Data exist”;

}
else
{
lblMessage.Text = “error in saving”;
}

}
protected void btnReset_Click(object sender, EventArgs e)
{
clear();
}
private void clear()
{
lblMessage.Text = “”;
txtAddress.Text = “”;
txtContact.Text = “”;
txtDateOfBirth.Text = “”;
txtEmail.Text = “”;
txtFirstName.Text = “”;
txtLastName.Text = “”;
txtPassword.Text = “”;
txtUserID.Text = “”;
lbtnFemale.Checked = false;
lbtnMale.Checked = false;
ddlOccupation.SelectedIndex = 0;

}

In this project we have created two-three classes for data-connectivity  and the connectionstring is added in web.config  file. To access connectionstring we uses configurationamanager.

See the web.config connectionstring part bellow:

<connectionStrings>
<add name=”conn” connectionString=”Data Source=.\sqlexpress;Initial Catalog=asdf;Integrated Security=True”/>
</connectionStrings>

And to use  connectionstring of web.config by using configurationmanager we declare a class named DataConnection where we have written a property named Con which Get sqlconnection. See the code snippet below.

private static SqlConnection SqlCon;

public static SqlConnection Con
{
get
{
return SqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings[“conn”].ConnectionString);

}
}

We also created store procedure in the project. The database is associated with the project. Download the project by clicking the link given at the end of the project.

See some code to handle store procedure of the database to save the records to the server.

public static int SaveUser(string Firstname,string Lastname,string Address,string Gender,string Contact,
string Email,string Occupation,DateTime dob,string userid,string password)
{
int n;
SqlCommand cmd = new SqlCommand();
try
{
cmd.Connection = DataConnection.Con;
cmd.CommandText = “sp_register”;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.Parameters.AddWithValue(“@Task”, “Save”);
cmd.Parameters.AddWithValue(“@id”, 0);
cmd.Parameters.AddWithValue(“@Firstname”, Firstname);
cmd.Parameters.AddWithValue(“@Lastname”, Lastname);
cmd.Parameters.AddWithValue(“@Address”, Address);
cmd.Parameters.AddWithValue(“@Gender”, Gender);
cmd.Parameters.AddWithValue(“@Contact”, Contact);
cmd.Parameters.AddWithValue(“@Email”, Email);
cmd.Parameters.AddWithValue(“@Occupation”, Occupation);
cmd.Parameters.AddWithValue(“@dob”, dob);
cmd.Parameters.AddWithValue(“@userid”, userid);
cmd.Parameters.AddWithValue(“@password”, password);
cmd.Parameters.Add(“@flag”, SqlDbType.Int).Direction = ParameterDirection.Output;

if (cmd.Connection.State == ConnectionState.Closed)
{
cmd.Connection.Open();
}
cmd.ExecuteNonQuery();
n = Convert.ToInt32(cmd.Parameters[“@flag”].Value);
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
return n;
}
catch (SqlException Ex)
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
return 0;
}
}

Now use this procedure to save the records by passing all the parameters defined in the store procedure,

Here is the code snippet:

int n = ClassUser.SaveUser(FirstName, LastName, Address, Gender, Contact, Email, Occupation, DateOfBirth, UserID, Password);
if (n == 1)
{

clear();
lblMessage.Text = “Data save successfully”;
Response.Redirect(“BookingApply.aspx”);
}
else if (n == -1)
{
lblMessage.Text = “Data exist”;

}
else
{
lblMessage.Text = “error in saving”;
}

Download the project but remember before running the project you need to attach the database to the server. And if you have any problem to attach database or any other problem, mail us @ query@sweksha.com.

 

Download link :

website with database connectivity

Happy coding.

 

3 Replies to “recall of sql connectivity in c# asp .net with detailed example”

Leave a Reply

Your email address will not be published. Required fields are marked *