sql creation

 sql server auth

creation 

https://kb.supremainc.com/knowledge/doku.php?id=en:1xfaq_how_to_create_a_sql_server_authentication_login_id

1. Create Procedure

ALTER PROCEDURE GetProfile

(

@userId NVARCHAR(250)

)

AS

BEGIN

SELECT 

[ASPNETUSERS].[Id]

, [UserName]

, [EmailConfirmed]

, [PhoneNumber]

, [PhoneNumberConfirmed]

, [TwoFactorEnabled]

, [AspNetRoles].[Name]

, [Address1]

, [Address2]

, [City]

, [State]

, [Landmark]

, [Pin]

, [CountryCode]

FROM [DBO].[ASPNETUSERS]

LEFT JOIN [dbo].[Profiles] ON [ASPNETUSERS].[Id] = [Profiles].[UserId]

LEFT JOIN [dbo].[AspNetUserRoles] ON [ASPNETUSERS].[Id] = [AspNetUserRoles].[UserId]

LEFT JOIN [dbo].[AspNetRoles] ON [AspNetUserRoles].[RoleId] = [AspNetRoles].[Id]

WHERE [AspNetUsers].[Id] = @userId

END

2. Create StoredProcedure Result Model

/// <summary>

/// Model to bind the result of getProfile stored procedure

/// </summary>

public class GetUserById

{

public string Id { get; set; }

public string UserName { get; set; }

public bool EmailConfirmed { get; set; }

public string PhoneNumber { get; set; }

public bool PhoneNumberConfirmed { get; set; }

public bool TwoFactorEnabled { get; set; }

public string Name { get; set; }

public string Address1 { get; set; }

public string Address2 { get; set; }

public string City { get; set; }

public string State { get; set; }

public string Landmark { get; set; }

public string Pin { get; set; }

public string CountryCode { get; set; }

}

3. Add Model in DBContext class

public DbSet<GetUserById> GetUserByIds { get; set; }

4. Create ActionMethod to call the procedure

/// <summary>

        /// Get profile

        /// </summary>

        /// <returns></returns>

        [HttpGet]

        [Route("Profile")]

        public async Task<IActionResult> GetProfile()

        {

            // Getting currently loggedIn userId

            var currentUserId = User.Claims.ToList().FirstOrDefault(x => x.Type == "id").Value;

            SqlParameter userIdParameter = new SqlParameter("@userId", currentUserId);

            var userProfile = (await databaseContext.GetUserByIds.FromSqlRaw("Exec GetProfile @userId", userIdParameter).ToListAsync()).FirstOrDefault();

            return Ok(userProfile);

        }

///////////////////////////////////////////////////////////////////////////////////////insert

  string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

                // SqlConnection is in System.Data.SqlClient namespace

                using (SqlConnection con = new SqlConnection(CS))

                {

                    SqlCommand cmd = new SqlCommand("spRegisterStudent", con);

                    cmd.CommandType = CommandType.StoredProcedure;

                    SqlParameter AdmissionNumber = new SqlParameter("@AdmissionNumber", txtAdmissionNumber.Text);

                    SqlParameter StudentName = new SqlParameter("@StudentName", txtStudentName.Text);

                    SqlParameter DateofBirth = new SqlParameter("@DateofBirth", txtBirthdate.Text);

                    SqlParameter FatherorMotherName = new SqlParameter("@FatherorMotherName", txtFatherorMotherName.Text);

                    SqlParameter studentclass = new SqlParameter("@StudentClass", Textclass.Text);

                    SqlParameter DateofJoining = new SqlParameter("@DateofJoining", txtDateofJoining.Text);

                    SqlParameter DateofLeaving = new SqlParameter("@DateofLeaving", txtDateofLeaving.Text);

                    SqlParameter Note = new SqlParameter("@Note", TextAreaNote.InnerText);

                    cmd.Parameters.Add(AdmissionNumber);

                    cmd.Parameters.Add(StudentName);

                    cmd.Parameters.Add(DateofBirth);

                    cmd.Parameters.Add(FatherorMotherName);

                    cmd.Parameters.Add(studentclass);

                    cmd.Parameters.Add(DateofJoining);

                    cmd.Parameters.Add(DateofLeaving);

                    cmd.Parameters.Add(Note);

                    con.Open();

                    int ReturnCode = (int)cmd.ExecuteScalar();

                    if (ReturnCode > 0)

                    {

                        con.Close();

                        lblMessage.Text = "Student Deatails of "+ txtAdmissionNumber.Text+" saved Successfully ";

                        lblMessage.ForeColor = System.Drawing.Color.Green;

                        btnClear_Click();

                    }

                    else

                    {

                        lblMessage.Text = "Student Deatails  " + txtAdmissionNumber.Text + "Already Exists";

                    }

///////////////////////////////////////////////////////////////////////////////byid

  string constr = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

                    SqlConnection con = new SqlConnection(constr);

                    SqlCommand cmd = new SqlCommand("GetbyAdmissionNumber", con);

                    cmd.CommandType = CommandType.StoredProcedure;

                    SqlParameter AdmissionNumber = new SqlParameter("@AdmissionNumber", Request.QueryString["AdmissionNumber"]);

                    cmd.Parameters.Add(AdmissionNumber);

                    con.Open();

                    SqlDataReader dataReader = cmd.ExecuteReader();

                    while (dataReader.Read())

                    {

                        txtAdmissionNumber.Text = dataReader["AdmissionNumber"].ToString();

                        txtStudentName.Text = dataReader["StudentName"].ToString();

                        txtBirthdate.Text = dataReader["DateofBirth"].ToString();

                        txtFatherorMotherName.Text = dataReader["FatherorMotherName"].ToString();

                        Textclass.Text = dataReader["studentclass"].ToString();

                        txtDateofJoining.Text = dataReader["DateofJoining"].ToString();

                        txtDateofLeaving.Text = dataReader["DateofLeaving"].ToString();

                        TextAreaNote.InnerText = dataReader["Note"].ToString();

                        txtAdmissionNumber.ReadOnly = true;

                        txtAdmissionNumber.BackColor = System.Drawing.Color.LightSlateGray;

                        txtStudentName.Focus();

                    }

//////////////////////////////////////////////////////////////////

 string constr = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

            using (SqlConnection con = new SqlConnection(constr))

            {

                using (SqlCommand cmd = new SqlCommand("STOREDPROCTUDENTDENTINFO"))

                {

                    using (SqlDataAdapter sda = new SqlDataAdapter())

                    {

                        cmd.CommandType = CommandType.StoredProcedure;

                        cmd.Connection = con;

                        sda.SelectCommand = cmd;

                        using (DataTable dt = new DataTable())

                        {

                            sda.Fill(dt);

                            GridView1.DataSource = dt;

                            GridView1.DataBind();

                        }

                    }

                }