Is this correct format for inserting values in database using parameterised query?

Question

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;

public partial class appointment : System.Web.UI.Page
{

SqlConnection con = new SqlConnection("Data Source=USER;Initial Catalog=Project;Integrated Security=True");  
string uname;  
protected void Page_Load(object sender, EventArgs e)  
{  
}  
protected void Button1_Click1(object sender, EventArgs e)  
{  
    uname = Session["email"].ToString();  
    con.Open();  
    SqlCommand cmd=new SqlCommand("insert into appointmt (patient__name,patient_email,doctor_name,doctor_email,treat_for,date,time)values(@patient_name, @patient_email,@doctor_name,@doctor_email,@treat_for,@date,@time)",con)  
    cmd.Parameters.AddWithValue("@patient_name",SqlDbType.VarChar).Value=TextBox2.Text;  
    cmd.Parameters.AddWithValue("@patient_email",SqlDbType.VarChar).Value=TextBox3.Text;  
    cmd.Parameters.AddWithValue("@doctor_name",SqlDbType.VarChar).Value=TextBox4.Text;  
    cmd.Parameters.AddWithValue("@doctor_email",SqlDbType.VarChar).Value=TextBox5.Text;  
    cmd.Parameters.AddWithValue("@treat_for",SqlDbType.VarChar).Value=TextBox6.Text;  
    cmd.Parameters.AddWithValue("@date",SqlDbType.Date).Value=TextBox7.Text;  
    cmd.Parameters.AddWithValue("@time",SqlDbType.Time).Value=DropDownList1.Text;  
    cmd.ExecuteScalar();  
    Response.Write("<script>alert('Booked successfully')</script>");  
    con.Close();  

}  

}

Answer 1

The parameter query pattern looks correct but there's no user input validation which can be problematic. Plus we cannot see the table schema or the markup. Have you tried executing the code? Does it work? Are you using the Visual Studio debugger to check your code?

First look at the Visual Studio Debugger

Going through a few Web Forms tutorials is more helpful for learning the fundamentals than asking for code reviews.

Getting Started with ASP.NET 4.5 Web Forms and Visual Studio 2017

Consider learning ASP.NET Core Razor Pages which is the latest framework.

Tutorial: Create a Razor Pages web app with ASP.NET Core
Razor Pages with Entity Framework Core in ASP.NET Core - Tutorial 1 of 8

Answer 2

I'd recommend not using AddWithValue, see the following.

A guideline to follow.

• Use a class/model, pass it to a dedicate method for an add record
• Have some form of exception handling in the event bad things happen e.g. server down etc
• For connection and command objects implement with using declarations or using statements.
• Return a named value tuple, success and an exception object or in your what suits your needs.
• Consider using Entity Framework Core, so much easier for web (Blazor, Razor etc)

Sample code done with C# 10/11 and note there are two quires, the insert and one to get the new key.

using System.ComponentModel.DataAnnotations;  
using System.Data;  
using Microsoft.Data.SqlClient;  
  
namespace SqlServerLibrary;  
  
public class DataOperations  
{  
    private static readonly string ConnectionString =  
        "TODO";  
  
    public static (bool success, Exception exception) AddCustomer(Customer customer)  
    {  
  
        using var cn = new SqlConnection(ConnectionString);  
  
        using var cmd = new SqlCommand  
        {  
            Connection = cn,  
            CommandText =  
                @"  
                    INSERT INTO dbo.Customer (CompanyName, ContactName, ContactTypeIdentifier, GenderIdentifier)   
                    VALUES (@CompanyName,  @ContactName,  @ContactTypeIdentifier,  @GenderIdentifier);  
                    SELECT CAST(scope_identity() AS int);"  
        };  
  
  
        cmd.Parameters.Add("@CompanyName", SqlDbType.NChar).Value =  
            customer.CompanyName;  
  
        cmd.Parameters.Add("@ContactName", SqlDbType.NChar).Value =  
            customer.ContactName;  
  
        cmd.Parameters.Add("@ContactTypeIdentifier", SqlDbType.Int).Value =  
            customer.ContactTypeIdentifier;  
  
        cmd.Parameters.Add("@GenderIdentifier", SqlDbType.Int).Value =  
            customer.GenderIdentifier;  
  
        try  
        {  
            cn.Open();  
  
            customer.Identifier = Convert.ToInt32(cmd.ExecuteScalar());  
            return (true, null);  
        }  
        catch (Exception localException)  
        {  
  
            return (false, localException);  
        }  
    }  
}  
  
public class Customer  
{  
    public int Identifier { get; set; }  
    [Required]  
    public string CompanyName { get; set; }  
    [Required]  
    public string ContactName { get; set; }  
    [Required]  
    public int? ContactTypeIdentifier { get; set; }  
    [Required]  
    public int? GenderIdentifier { get; set; }  
    public override string ToString() => CompanyName;  
}  

Then a sample call not fully done in regards to setting the Customer properties

Customer customer = new ();  
var (success, exception) = DataOperations.AddCustomer(customer);  
if (success)  
{  
    // the customer object has a assigned primary key  
}  
else if (exception is not null)  
{  
    // does something with the exception e.g. log it, tell user...  
}