Step by Step SQL Murat Gungor 552004 Step

  • Slides: 20
Download presentation
Step by Step SQL Murat Gungor 5/5/2004

Step by Step SQL Murat Gungor 5/5/2004

Step by Step SQL • • Installation SQL Server to your computer Running SQL

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

Installation SQL Server 1/4 • If your computer does not have SQL Server installed:

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/topdownloads. asp And download “Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Release A” • Before installing “MSDE 2000 A. exe” make sure that “File and Print Sharing for Microsoft Networks” is selected. 3

Installation SQL Server 2/4 • In Control Panel, double-click Network Connections. • On the

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. 4

Installation SQL Server 3/4 – – In Control Panel, double-click Administrative Tools. Double-click Local

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. 5

Installation SQL Server 4/4 After Unzipping it to directory say: C: MSDERel. A Run

Installation SQL Server 4/4 After Unzipping it to directory say: C: MSDERel. A Run install SQL server with following command line: setup INSTANCENAME=“NETSDK" SECURITYMODE=SQL SAPWD=“My. Password" /L*v C: /MSDELog. log You can personalize the INSTANCENAME and SAPWD, result of installation will be C: /MSDELog. log 6

Run SQL Server After installation: • In Control Panel, double-click – Administrative Tools. •

Run SQL Server After installation: • In Control Panel, double-click – Administrative Tools. • In Administrative Tools, double-click – Services • And START – MSSQL$NETSDK 7

Turn the security off in LAB ØGo the following directory C: WINDOWSMicrosoft. NET Frameworkv

Turn the security off in LAB ØGo the following directory C: WINDOWSMicrosoft. NET Frameworkv 1. 1. 4322 ØRight click on “Caspol. exe” from the menu chose “Create Shortcut” ØAnd right click on “Shortcut to Cas. Pol. exe” ØType at the end of Target “–s off” Click OK ØDouble click to shortcut, ØYou should see “success” at the black screen. 8

Build C# Windows Application Open Visual Studio. NET and create Windows Application 9

Build C# Windows Application Open Visual Studio. NET and create Windows Application 9

Add New Database Server Explorer Right Click on NETSDK Then New Database resides at

Add New Database Server Explorer Right Click on NETSDK Then New Database resides at the following path: C: Program FilesMicrosoft SQL ServerMSSQL$NETSDK 10

Add New Table to Database Right Click on Tables to add New Table hit

Add New Table to Database Right Click on Tables to add New Table hit Save Button, and name it “Student” 11

Add Text. Boxs and Buttons Using Tool. Box add Buttons and Text. Boxes one

Add Text. Boxs and Buttons Using Tool. Box add Buttons and Text. Boxes one of them is Multi. Line Text. Box, Double Click each button to add methods for them. 12

Drag and drop Student Table over the form Using Server Explorer, find the Student

Drag and drop Student Table over the form Using Server Explorer, find the Student Table. Hold Student table and drop over Form 1 Hit OK 13

Coding Starts Connect to SQL Server 1 Add Connect. To. SQLDB function, as shown

Coding Starts Connect to SQL Server 1 Add Connect. To. SQLDB function, as shown below private void Connect. To. SQLDB() { try { sql. Connection 1. Connection. String= "integrated security=SSPI; ” + ”user id=sa; ” + ”password=; ” + “initial catalog=My. Database; ” + “data source=SYRU 208 -092\NETSDK"; //mach\SQLserver sql. Connection 1. Open(); text. Box 1. Text +="Connection Established"; } catch (Exception ex) { text. Box 1. Text = ex. Message; } } 14

Reading from SQL Database 2 Add Get. Student. Info function; private void Get. Student.

Reading from SQL Database 2 Add Get. Student. Info function; private void Get. Student. Info(){ string s. Sql. Cmd = "SELECT * FROM Student"; text. Box 2. Text = ""; try { // Get the data stream System. Data. Sql. Client. Sql. Data. Reader dr; System. Data. Sql. Client. Sql. Command my. SQLCommand; my. SQLCommand = new System. Data. Sql. Client. Sql. Command( s. Sql. Cmd, sql. Connection 1 ); dr = my. SQLCommand. Execute. Reader(); // Iterate through all returned records while( dr. Read() ) { text. Box 2. Text += dr["Name"] + " / "; text. Box 2. Text += dr["Phone"]+ " / "; text. Box 2. Text += dr["SUID"]+ "rn"; } dr. Close(); } catch( Exception ex ) { text. Box 2. Text += "rn } } ERROR: " + ex. Message ; 15

Connect Button 3 Add Connect Button Click function; private void Connect_Click(object sender, System. Event.

Connect Button 3 Add Connect Button Click function; private void Connect_Click(object sender, System. Event. Args e) { if (sql. Connection 1. State == Connection. State. Closed) { Connect. To. SQLDB(); Connect. Text = "Disconnect"; Get. Student. Info(); } else { sql. Connection 1. Close(); text. Box 1. Text ="Connection Closed"; Connect. Text = "Connect"; text. Box 2. Text=" "; } } 16

Add Button 4 Add Button Click function; private void Add_Click(object sender, System. Event. Args

Add Button 4 Add Button Click function; private void Add_Click(object sender, System. Event. Args e) { sql. Insert. Command 1. Parameters["@Name"]. Value = text. Box 3. Text; sql. Insert. Command 1. Parameters["@Phone"]. Value = text. Box 4. Text; sql. Insert. Command 1. Parameters["@SUID"]. Value = text. Box 5. Text; sql. Insert. Command 1. Execute. Non. Query(); //sql. Data. Adapter 1. Insert. Command. Execute. Non. Query(); text. Box 1. Text ="Insertion has been made"; Get. Student. Info(); } 17

Remove Button 5 Add Remove Button Click function; private void Remove_Click(object sender, System. Event.

Remove Button 5 Add Remove Button Click function; private void Remove_Click(object sender, System. Event. Args e) { sql. Delete. Command 1. Parameters["@Original_Name"]. Value = text. Box 3. Text; sql. Delete. Command 1. Parameters["@Original_Phone"]. Value= text. Box 4. Text; sql. Delete. Command 1. Parameters["@Original_SUID"]. Value = text. Box 5. Text; sql. Delete. Command 1. Execute. Non. Query(); text. Box 1. Text ="Data has been deleted"; Get. Student. Info(); } 18

End of presentation 19

End of presentation 19

Extra Details of sql. Delete. Command 1 System. Data. Sql. Client. Sql. Command sql.

Extra Details of sql. Delete. Command 1 System. Data. Sql. Client. Sql. Command sql. Delete. Command 1 = new System. Data. Sql. Client. Sql. Command(); sql. Delete. Command 1. Command. Text = "DELETE FROM Student WHERE (Name = @Original_Name) AND (Phone = @Original_Phone OR" + " @Original_Phone IS NULL AND Phone IS NULL) AND (SUID = @Original_SUID OR @Origi" + "nal_SUID IS NULL AND SUID IS NULL)"; sql. Delete. Command 1. Connection = this. sql. Connection 1; sql. Delete. Command 1. Parameters. Add(new System. Data. Sql. Client. Sql. Parameter("@Original_Name", System. Data. Sql. Db. Type. Var. Char, 30, System. Data. Parameter. Direction. Input, false, ((System. Byte)(0)), "Name", System. Data. Row. Version. Original, null)); sql. Delete. Command 1. Parameters. Add(new System. Data. Sql. Client. Sql. Parameter("@Original_Phone", System. Data. Sql. Db. Type. Var. Char, 30, System. Data. Parameter. Direction. Input, false, ((System. Byte)(0)), "Phone", System. Data. Row. Version. Original, null)); sql. Delete. Command 1. Parameters. Add(new System. Data. Sql. Client. Sql. Parameter("@Original_SUID", System. Data. Sql. Db. Type. Var. Char, 30, System. Data. Parameter. Direction. Input, false, ((System. Byte)(0)), "SUID", System. Data. Row. Version. Original, null)); 20