Insert , Delete , Search , Login

Insert

aspx.cs

protected void btnAdd_Click(object sender, EventArgs e)
    {
        ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["dbConnection"];
        SqlConnection strConnString = new SqlConnection(connectionStringSettings.ConnectionString);
        DbConnectProp property = new DbConnectProp();
        string insert_command = "_a_insert";
        property.CountryID=int.Parse(txtCountryID.Text);
        property.CountryIDString = txtCountryName.Text;
        SqlCommand newinsert = new SqlCommand(insert_command, strConnString);
        newinsert.CommandType = CommandType.StoredProcedure;
        newinsert.Parameters.Add(new SqlParameter("@CountryId", SqlDbType.Int)).Value = property.CountryID;
        newinsert.Parameters.Add(new SqlParameter("@CountryName", SqlDbType.VarChar, 100)).Value = property.CountryIDString;
        newinsert.Connection.Open();
        newinsert.ExecuteNonQuery();
        newinsert.Connection.Close();


    }

Web.config

<connectionStrings>
    <add name="dbConnection" connectionString="Data Source=VIRTUSA7\SQLEXPRESS;Initial Catalog=SoBiz10DB;Integrated Security=True" providerName="System.Data.SqlClient"/>
      </connectionStrings>

Stored Procedure

ALTER PROCEDURE [dbo].[_a_insert]
(
@CountryId int,
@CountryName Varchar(100)
)
AS
BEGIN
    INSERT INTO dbo.CountriesList(CountryId ,CountryName ) VALUES (@CountryId , @CountryName)
END


Property

 public class DbConnectProp
    {
        private int countryID;

        public int CountryID
        {
            get { return countryID; }
            set { countryID = value; }
        }

        private string countryIDstring;

        public string CountryIDString
        {
            get { return countryIDstring; }
            set { countryIDstring = value; }
        }
    }



Delete

aspx.cs

    protected void btnDelete_Click(object sender, EventArgs e)
    {
        DbConnectProp property = new DbConnectProp();
        ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["dbConnection"];
        SqlConnection strConnString = new SqlConnection(connectionStringSettings.ConnectionString);
       
        property.CountryID = int.Parse(txtCountryID.Text);
        myCommand = new SqlDataAdapter("SELECT * FROM CountriesList WHERE CountryId =" + property.CountryID, strConnString);
        dt = new DataTable();
        myCommand.Fill(dt);
        if (dt.Rows.Count > 0)
        {
            string insert_command = "_a_delete";
            SqlCommand newinsert = new SqlCommand(insert_command, strConnString);
            newinsert.CommandType = CommandType.StoredProcedure;
            newinsert.Parameters.Add(new SqlParameter("@CountryId", SqlDbType.Int)).Value = property.CountryID;
            SqlDataAdapter mycommand1 = new SqlDataAdapter();
            mycommand1.DeleteCommand = newinsert;
            newinsert.Connection.Open();
            newinsert.ExecuteNonQuery();
            newinsert.Connection.Close();
        }
        else
        {
            Page.Controls.Add(new LiteralControl(
                              "<script language='javascript'> window.alert('No Records')</script>"));
        }

Web.config

<connectionStrings>
    <add name="dbConnection" connectionString="Data Source=VIRTUSA7\SQLEXPRESS;Initial Catalog=SoBiz10DB;Integrated Security=True" providerName="System.Data.SqlClient"/>
      </connectionStrings>


Stored Procedure

ALTER PROCEDURE [dbo].[_a_delete]
(
@CountryId int
)   
AS
BEGIN
    DELETE dbo.CountriesList
    WHERE CountryId=@CountryId
END

Property

 public class DbConnectProp
    {
        private int countryID;

        public int CountryID
        {
            get { return countryID; }
            set { countryID = value; }
        }

        private string countryIDstring;

        public string CountryIDString
        {
            get { return countryIDstring; }
            set { countryIDstring = value; }
        }
    }


Search

aspx.cs

protected void btnSearch_Click(object sender, EventArgs e)
    {
        DbConnectProp property = new DbConnectProp();
        ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["dbConnection"];
        SqlConnection strConnString = new SqlConnection(connectionStringSettings.ConnectionString);
        property.CountryID = int.Parse(txtCountryID.Text);
        string insert_command = "_a_search";
        SqlCommand newinsert = new SqlCommand(insert_command, strConnString);
        newinsert.CommandType = CommandType.StoredProcedure;
        newinsert.Parameters.Add(new SqlParameter("@CountryId", SqlDbType.Int)).Value = property.CountryID;
        newinsert.Connection.Open();
        myCommand = new SqlDataAdapter();
        myCommand.SelectCommand = newinsert;
        dt = new DataTable();
        myCommand.Fill(dt);
        if (dt.Rows.Count > 0)
        {
            property.CountryID = int.Parse(dt.Rows[0].ItemArray.GetValue(dt.Columns["CountryId"].Ordinal).ToString());
            property.CountryIDString = dt.Rows[0].ItemArray.GetValue(dt.Columns["CountryName"].Ordinal).ToString();
            txtCountryID.Text = property.CountryID.ToString();
            txtCountryName.Text = property.CountryIDString;
            newinsert.ExecuteNonQuery();
            newinsert.Connection.Close();
        }
        else
        {
            Page.Controls.Add(new LiteralControl(
                   "<script language='javascript'> window.alert('No Records')</script>"));
        }
    }

Stored Procedure

ALTER PROCEDURE [dbo].[_a_search]
(
@CountryId int
)
AS
BEGIN
    SELECT CountryId , CountryName
    FROM dbo.CountriesList
    WHERE CountryId = @CountryId
END


Login

aspx.cs

protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
    {
        if (login(Login1.UserName, Login1.Password))
        {
            Response.Redirect("http://www.cricinfo.com");
       
        }
    }


    private bool login(string username, string password)
    {
        bool boolReturn = false;
        ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["dbConnection"];
        SqlConnection strConnString = new SqlConnection(connectionStringSettings.ConnectionString);
        string abc = "_a_select";
        SqlCommand command = new SqlCommand(abc, strConnString);
        myCommand = new SqlDataAdapter();
        myCommand.SelectCommand = command;
        command.CommandType = CommandType.StoredProcedure;
        dt = new DataTable();
        myCommand.Fill(dt);

        //myCommand = new SqlDataAdapter("SELECT * FROM SoBizUsers WHERE SoBizLoginName= '" + Login1.UserName + "' AND SoBizPassword= '"+ Login1.Password+"'", strConnString);
        //dt = new DataTable();
        //myCommand.Fill(dt);
        //if (dt.Rows.Count > 0)
        //{
        //    boolReturn = true;
        //}
        return boolReturn;

    }


Stored Procedure

ALTER PROCEDURE [dbo].[_a_login]
(
@SoBizLoginName varchar(100),
@SoBizPassword varchar(100)
)
AS
BEGIN
   
    SELECT *
    FROM dbo.SoBizUsers
    WHERE SoBizLoginName= @SoBizLoginName  AND SoBizPassword=@SoBizPassword
END