how to get database path insertion query

 · HOW TO GET DATABASE PATH

STEP 1 : SELECT SQL DATA SOURCE CONTROL

STEP 2 : CLICK ARROW OPERATOR

STEP 3 :  SELECT CONFIGURE DATASOURCE OPTION

STEP 4 : CLICK CHECK BOX

STEP 5 : COPY PATH

Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\COLLEGE.mdf;Integrated Security=True

 

 

· DATABASE PATH FINDER

( @ ) OPERATOR IS CALLED DATABASE PATH FINDER

 

 

· HOW TO PASS CONTROL INSIDE THE QUERY STATEMENT (IMP)

THERE ARE FOLLOWING METHOS USED TO PASS DOTNET CONTROL INSIDE THE QUERY STATEMENT

1) PARAMETERISE QUERY METHOD :-

IN THIS METHOD WE CREATE PARAMETERISE VARIABLE INSIDE THE QUERY STATEMENT

ALL PARAMETER IS TO BE CREATED USING @ SYMBOL

 

2) CLASS AND OBJECT METHOD

3) STORE PROCEDURE METHOD

4) 3 TIER ARCHITECTURE METHOD

 

· HOW TO PASS CONTROL INSIDE THE PARAMETER

IN THIS CONCEPT WE USE AddWithValue();

 

SYNTAX :

commandname.PARAMETERS.AddWithValue(VARIABLE,CONTROL);

 

EX :

comm.Parameters.AddWithValue("ROLL1", TextBox1.Text);

comm.Parameters.AddWithValue("NAME1", TextBox2.Text);

comm.Parameters.AddWithValue("CITY1", TextBox3.Text);

 

 

Ø CODE INSERT

Ø             //CODE METHOD

Ø             //STEP 1 (OPEN DATABASE)

Ø             string PATH = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\COLLEGE.mdf;Integrated Security=True";

Ø             connect = new SqlConnection(PATH);

Ø             connect.Open();

Ø 

Ø             //STEP 2 (APPLY COMMAND) QUERY

Ø             string INS = "INSERT INTO CONNECTDB (ROLL,NAME,CITY) VALUES (@ROLL1,@NAME1,@CITY1)";      //INSERT INTO CONNECTDB VALUES (18,'VIRAT','DELHI');

Ø             comm = new SqlCommand(INS,connect); //(VARIABLE, CONNECTION                OBJECT)

Ø 

Ø             //PASS CONTROL TO PARAMETER

Ø             comm.Parameters.AddWithValue("ROLL1", TextBox1.Text);

Ø             comm.Parameters.AddWithValue("NAME1", TextBox2.Text);

Ø             comm.Parameters.AddWithValue("CITY1", TextBox3.Text);

Ø 

Ø             //STEP 3 (EXECUTE COMMAND)

Ø             comm.ExecuteNonQuery();

Ø 

Ø             Response.Write("RECORD SAVED SUCCESSFULLY");

Ø 

Ø             TextBox1.Text = "";

Ø             TextBox2.Text = "";

Ø             TextBox3.Text = "";

 

 

Ø CODE DELETE

Ø             //OPEN CONNECTION

Ø             string PATH = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\COLLEGE.mdf;Integrated Security=True";

Ø             connect = new SqlConnection(PATH);

Ø             connect.Open();

Ø 

Ø             //APPLY COMMAND

Ø             string DLT = "DELETE FROM CONNECTDB WHERE ROLL=@ROLL1";

Ø             comm = new SqlCommand(DLT, connect);

Ø 

Ø             //PASS CONTROL

Ø             comm.Parameters.AddWithValue("ROLL1",TextBox1.Text);

Ø 

Ø             //EXECUTE NON QUERY

Ø             comm.ExecuteNonQuery();

Ø 

Ø             Response.Write("RECORD DELETED");

Ø 

Ø             TextBox1.Text = "";

 

 

 

Ø CODE UPDATE

Ø             //OPEN CONNECTION

Ø             string PATH = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\COLLEGE.mdf;Integrated Security=True";

Ø             connect = new SqlConnection(PATH);

Ø             connect.Open();

Ø 

Ø             //APPLY COMMAND

Ø             string UPD = "UPDATE CONNECTDB SET NAME=@NAME1 WHERE ROLL=@ROLL1";

Ø             comm = new SqlCommand(UPD,connect);

Ø 

Ø             //PASS CONTROL

Ø             comm.Parameters.AddWithValue("NAME1", TextBox2.Text);

Ø             comm.Parameters.AddWithValue("ROLL1", TextBox1.Text);

Ø 

Ø             //EXECUTE QUERY

Ø             comm.ExecuteNonQuery();

Ø 

Ø             Response.Write("UPDATE SUCCESSFULLY");

Ø 

Ø             TextBox1.Text = "";

Ø             TextBox2.Text = ""; 

Comments

Post a Comment

Popular posts from this blog

ViewState for state management

  v  ViewState :- I.  It is used to store data value in a single webpage. II.  The ViewState cannot transfer the  data between one page to another page . v  How to create ViewState? Sytanx:- ViewState[“var”]=Control; Ex.- ViewState[“Email”]=Textbox1.text;   v  How to access ViewState data value? Syntax:-control=ViewState[“var”].Tostring(); Ex:-Label1.text=ViewState[“Email”].tostring();   Code:- using  System; using  System.Collections.Generic; using  System.Linq; using  System.Web; using  System.Web.UI; using  System.Web.UI.WebControls;   namespace  WebApplication1 {      public   partial   class   stateview  : System.Web.UI.Page     {          protected   void  Page_Load( object  sender, EventArgs e)         {        ...
Read more

Store procedure Data Base handling (procedure_insert).

Image
  Ø  Store procedure Data Base handling  (procedure_insert). 1.  Include procdure library (system.data). 2.  Pass procedure name to sqlcommand parameters. Syntax :- com= new sqlcommand(“procedure name”,connection object) 3.  Set the command type (store procedure) Syntax :-com.CommandType=commandtype.storeprocedure     STORE PROCEDURE CREATE   PROCEDURE  PROCON   ( @ROLL1 AS    INT   ,  @NAME1 AS   VARCHAR ( 50 ))   AS     BEGIN   INSERT   INTO  DEMO   ( ROLL ,   NAME   )   VALUES ( @ROLL1 ,  @NAME1 )     END   ;             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   ...
Read more

Database joining

Image
  l  Database joining :- 1.  Joining is a special type of operation perform between 2 tables. 2.  With the help of joining we get the result between multiple data. l  Joining property:- 1.  Common field (column) 2.  Using table object Syntax :-  table name.column name 3.  To activate joining operaton we need  ON  KEYWORD. ü  TYPES OF JOINING:- 1.  INNER JOIN  :- Inner join returns all records from multiple data where the join condition is equal. Ex:- CREATE   TABLE  invent   (  id INT   ,   name   VARCHAR ( 20 )); INSERT   INTO  invent values ( 101 , 'rahul' ),( 103 , 'thosar' ),( 102 , 'asus' );   CREATE   TABLE  product   ( id INT   ,  cost int );   INSERT   INTO  product VALUES ( 201 , '1000' ),( 202 , '2000' ),( 203 , '30000' );   SELECT  invent . id ,  invent . name ,  product . cost FROM  invent INN...
Read more