Murat Gungor

1
Step by Step SQL

• Murat Gungor
• 5/5/2004

2
Step by Step SQL

• Installation SQL Server to your computer
• Running SQL Server
• Building simple data table
• Demo program to access to data table

3
Installation SQL Server 1/4

• If your computer does not have SQL Server
  installed Go to web site below
• http//www.microsoft.com/sql/downloads/topdownload
  s.asp
• And download Microsoft SQL Server 2000 Desktop
  Engine (MSDE 2000) Release A
• Before installing MSDE2000A.exe make sure that
  File and Print Sharing for Microsoft Networks
  is selected.

4
Installation SQL Server 2/4

• In Control Panel, double-click Network
  Connections.
• On the Advanced menu, click Advanced Settings.
• On the General tab, ensure that File and Print
  Sharing for Microsoft Networks is selected.

5
Installation SQL Server 3/4

• In Control Panel, double-click Administrative
  Tools.
• Double-click Local Security Policy.
• Expand Local Policies.
• Select Security Options.
• Ensure that the following option in the right
  pane is set to Silently Succeed before installing
  MSDE 2000 Release A
• On Windows XP and Windows 2003
• Devices Unsigned driver installation behavior.
• On Windows 2000
• Unsigned non-driver installation behavior.

6
Installation SQL Server 4/4

• After Unzipping it to directory say C\MSDERelA
• Run install SQL server with following command
  line
• setup INSTANCENAMENETSDK" SECURITYMODESQL
  SAPWDMyPassword" /Lv C/MSDELog.log
• You can personalize the INSTANCENAME and SAPWD,
  result of installation will be C/MSDELog.log

7
Run SQL Server

• After installation
• In Control Panel, double-click
• Administrative Tools.
• In Administrative Tools, double-click
• Services
• And START
• MSSQLNETSDK

8
Turn the security off in LAB

• Go the following directory
• C\WINDOWS\Microsoft.NET\Framework\v1.1.4322
• Right click on Caspol.exe from the menu chose
  Create Shortcut
• And right click on Shortcut to CasPol.exe
• Type at the end of Target s off Click
  OK
• Double click to shortcut,
• You should see success at the black screen.

9
Build C Windows Application

• Open Visual Studio .NET and create
• Windows Application

10
Add New Database

• Server Explorer
• Right Click on NETSDK
• Then New Database

Database resides at the following
path C\Program Files\Microsoft SQL
Server\MSSQLNETSDK
11
Add New Table to Database

• Right Click on Tables to add New Table
• hit Save Button, and name it Student

12
Add TextBoxs and Buttons

• Using ToolBox add Buttons and TextBoxes one of
  them is MultiLine TextBox, Double Click each
  button to add methods for them.

13
Drag and drop Student Table over the form

• Using Server Explorer, find the Student Table.
  Hold Student table and drop over Form1
• Hit OK

14
Coding Starts Connect to SQL Server 1
Add ConnectToSQLDB function, as shown below

• private void ConnectToSQLDB()
• try
• sqlConnection1.ConnectionString "integrated
  securitySSPI
• user idsa
• password
• initial catalogMyDatabase
• data sourceSYRU208-092\\NETSDK" //mach\\SQLserv
  er
• sqlConnection1.Open()
• textBox1.Text "Connection Established"
• catch (Exception ex)
• textBox1.Text ex.Message

15
Reading from SQL Database 2
Add GetStudentInfo function

• private void GetStudentInfo()
• string sSqlCmd "SELECT FROM Student"
• textBox2.Text ""
• try
• // Get the data stream
• System.Data.SqlClient.SqlDataReader dr
• System.Data.SqlClient.SqlCommand mySQLCommand
• mySQLCommand new System.Data.SqlClient.SqlComma
  nd( sSqlCmd, sqlConnection1 )
• dr mySQLCommand.ExecuteReader()
• // Iterate through all returned records
• while( dr.Read() )
• textBox2.Text dr"Name" " / "
• textBox2.Text dr"Phone" " / "
• textBox2.Text dr"SUID" "\r\n"
• dr.Close()

16
Connect Button 3
Add Connect Button Click function

• private void Connect_Click(object sender,
  System.EventArgs e)
• if (sqlConnection1.State ConnectionState.Closed
  )
• ConnectToSQLDB()
• Connect.Text "Disconnect"
• GetStudentInfo()
• else
• sqlConnection1.Close()
• textBox1.Text "Connection Closed"
• Connect.Text "Connect"
• textBox2.Text" "

17
Add Button 4
Add Add Button Click function

• private void Add_Click(object sender,
  System.EventArgs e)
• sqlInsertCommand1.Parameters"_at_Name".Value
  textBox3.Text
• sqlInsertCommand1.Parameters"_at_Phone".Value
  textBox4.Text
• sqlInsertCommand1.Parameters"_at_SUID".Value
  textBox5.Text
• sqlInsertCommand1.ExecuteNonQuery()
• //sqlDataAdapter1.InsertCommand.ExecuteNonQuery()
  
• textBox1.Text "Insertion has been made"
• GetStudentInfo()

18
Remove Button 5
Add Remove Button Click function

• private void Remove_Click(object sender,
  System.EventArgs e)
• sqlDeleteCommand1.Parameters"_at_Original_Name".Val
  ue textBox3.Text
• sqlDeleteCommand1.Parameters"_at_Original_Phone".Va
  lue textBox4.Text
• sqlDeleteCommand1.Parameters"_at_Original_SUID".Val
  ue textBox5.Text
• sqlDeleteCommand1.ExecuteNonQuery()
• textBox1.Text "Data has been deleted"
• GetStudentInfo()

19
End of presentation
20
Extra Details of sqlDeleteCommand1

• System.Data.SqlClient.SqlCommand
  sqlDeleteCommand1 new System.Data.SqlClient.SqlC
  ommand()
• sqlDeleteCommand1.CommandText "DELETE FROM
  Student WHERE (Name _at_Original_Name) AND (Phone
  _at_Original_Phone OR"
• " _at_Original_Phone IS NULL AND Phone IS NULL) AND
  (SUID _at_Original_SUID OR _at_Origi"
• "nal_SUID IS NULL AND SUID IS NULL)"
• sqlDeleteCommand1.Connection this.sqlConnection1
  
• sqlDeleteCommand1.Parameters.Add(new
  System.Data.SqlClient.SqlParameter("_at_Original_Name
  ", System.Data.SqlDbType.VarChar, 30,
  System.Data.ParameterDirection.Input, false,
  ((System.Byte)(0)), ((System.Byte)(0)), "Name",
  System.Data.DataRowVersion.Original, null))
• sqlDeleteCommand1.Parameters.Add(new
  System.Data.SqlClient.SqlParameter("_at_Original_Phon
  e", System.Data.SqlDbType.VarChar, 30,
  System.Data.ParameterDirection.Input, false,
  ((System.Byte)(0)), ((System.Byte)(0)), "Phone",
  System.Data.DataRowVersion.Original, null))
• sqlDeleteCommand1.Parameters.Add(new
  System.Data.SqlClient.SqlParameter("_at_Original_SUID
  ", System.Data.SqlDbType.VarChar, 30,
  System.Data.ParameterDirection.Input, false,
  ((System.Byte)(0)), ((System.Byte)(0)), "SUID",
  System.Data.DataRowVersion.Original, null))