reg

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Events_diplomna_rabota
{
    public partial class Registration : System.Web.UI.Page
    {
        public static SqlConnection CreateConnection()
        {
            SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Events_12118071ConnectionString"].ConnectionString);
            return connection;
        }

        protected void Page_Load(object sender, EventArgs e)
        {

        }

        private static String checkUserExistsSQL =
            "SELECT COUNT(*) FROM login WHERE user_name = @user_name";

        //private static String registerUserInfoSQL =
        //    "INSERT INTO " +
        //    "   users (fname, lname, email) " +
        //    "VALUES " +
        //    " (@fname, @lname, @email) ";

        //private static String extractNewUserIdSQL =
        //    "SELECT user_id FROM users WHERE fname = @fname AND lname = @lname AND email = @email";

        //private static String registerUserSQL =
         //   "INSERT INTO login(login_id, user_name, password) VALUES(@user_id, @username, @password)";

        protected void btnReg_Click(object sender, EventArgs e)
        {
            if (txtUserName.Text.Length > 0 &&
            txtPass.Text.Length > 0 &&
            txtLName.Text.Length > 0 &&
            txtName.Text.Length > 0 &&
            txtEmail.Text.Length > 0)
            {
                SqlConnection connection = CreateConnection();
                using (connection)
                {
                    SqlCommand checkUsernameExistsCmd = new SqlCommand(checkUserExistsSQL, connection);
                    SqlParameter user_name = new SqlParameter("@user_name", SqlDbType.VarChar);
                    user_name.Value = txtUserName.Text.ToLower();
                    checkUsernameExistsCmd.Parameters.Add(user_name);

                    try
                    {
                        connection.Open();
                        object res = checkUsernameExistsCmd.ExecuteScalar();
                        if ((int)res > 0)
                        {
                            lbMessage.Text = "Вече съществува потребител с такова име (" + txtUserName.Text + ")!";
                        }
                        else
                        {
                            //SqlCommand registerUserInfoCmd =
                            //new SqlCommand(registerUserInfoSQL, connection);
                            SqlCommand registerUserInfoCmd = new SqlCommand("sp_insert_users", connection);
                            registerUserInfoCmd.CommandType = CommandType.StoredProcedure;
                            registerUserInfoCmd.CommandText = "sp_insert_users";

                            SqlParameter fname = new SqlParameter("@fname", SqlDbType.VarChar);
                            fname.Value = txtName.Text;
                            registerUserInfoCmd.Parameters.Add(fname);

                            SqlParameter lname = new SqlParameter("@lname", SqlDbType.VarChar);
                            lname.Value = txtLName.Text;
                            registerUserInfoCmd.Parameters.Add(lname);

                            SqlParameter email = new SqlParameter("@email", SqlDbType.VarChar);
                            email.Value = txtEmail.Text;
                            registerUserInfoCmd.Parameters.Add(email);

                            //registerUserInfoCmd.ExecuteNonQuery();


                            //SqlCommand getUserIdCmd = new SqlCommand(extractNewUserIdSQL, connection);
                            //getUserIdCmd.Parameters.Add(fname);
                            //getUserIdCmd.Parameters.Add(lname);
                            //getUserIdCmd.Parameters.Add(email);
                            int user_id = Convert.ToInt32(registerUserInfoCmd.ExecuteScalar());
                            registerUserInfoCmd.Parameters.Clear();

                            //SqlCommand registerCmd = new SqlCommand(registerUserSQL, connection);
                            SqlCommand registerCmd = new SqlCommand("sp_insert_login", connection);
                            registerCmd.CommandType = CommandType.StoredProcedure;
                            registerCmd.CommandText = "sp_insert_login";

                            SqlParameter userid = new SqlParameter("@user_id", SqlDbType.Int);
                            userid.Value = user_id;
                            registerCmd.Parameters.Add(userid);

                            SqlParameter username = new SqlParameter("@username", SqlDbType.VarChar);
                            username.Value = txtUserName.Text;
                            registerCmd.Parameters.Add(username);

                            SqlParameter password = new SqlParameter("@password", SqlDbType.VarChar);
                            password.Value = txtPass.Text;
                            registerCmd.Parameters.Add(password);

                            registerCmd.ExecuteNonQuery();

                            lbMessage.Text = "Регистрацията премина успешно!";
                        }
                    }
                    catch (SqlException ol)
                    {
                        lbMessage.Text = "Неуспешна регистрация: " + ol.Message;
                    }
                }
            }
        }

        protected void btnCancel_Click(object sender, EventArgs e)
        {
            Response.Redirect("Home.aspx");
        }
    }
}