using System;

using System.Data;

using System.Windows.Forms;

using System.Data.SqlClient;

using System.Configuration;

namespace L17

{

public partial class Form1 : Form

    {

SqlConnection connection1, connection2;

public Form1()

        {

            InitializeComponent();

// string cs = @"Data Source=B325\SQL2012EXPRESS;Initial Catalog=test2;Integrated security=True;MultipleActiveResultSets=True";

string cs = ConfigurationManager.ConnectionStrings["con1"].ConnectionString;

            connection1 = new SqlConnection(cs);

            connection1.Open();

//  connection2 = new SqlConnection(cs);

//  connection2.Open();

        }

private void button1_Click(object sender, EventArgs e)

        {

//CREATE TABLE

string s1 = @"CREATE TABLE BANK (id int, nazwisko nvarchar(20), imie nchar(20), kwota int)";

SqlCommand cmd1 = new SqlCommand(s1, connection1);

            cmd1.ExecuteNonQuery();

//INSERT

string s2 = @"INSERT INTO BANK (id, nazwisko, imie, kwota) VALUES

             (1,'Wróbel', 'Jan', 1200),(2,'Sroka', 'Piotr', 800),(3,'Bocian', 'Jan', 800),(4,'Czajka', 'Jan', 123),

             (5,'Orzeł', 'Jan',0),(6,'Sikora', 'Piotr',0),(7,'Sowa', 'Jan',12312),(8,'Mewa', 'Piotr',0)";

SqlCommand cmd2 = new SqlCommand(s2, connection1);

            cmd2.ExecuteNonQuery();

//CREATE TABLE

string s3 = @"CREATE TABLE SAMOCHOD3 (marka nvarchar(20), VIN int, userId int)";

SqlCommand cmd3 = new SqlCommand(s3, connection1);

            cmd3.ExecuteNonQuery();

//INSERT

string s4 = @"INSERT INTO SAMOCHOD3 (marka, VIN, userId) VALUES ('FORD', 44688221, 1),('FIAT',44688222,2),

                         ('VOLVO',44681223,3),('FIAT',44688224,4),('Mercedes',44688225,7),('FIAT',44688223,8),

                         ('FIAT',44618223,9),('Skoda',49688223,10)";

SqlCommand cmd4 = new SqlCommand(s4, connection1);

            cmd4.ExecuteNonQuery();        }

private void button4_Click(object sender, EventArgs e)

        {

string s4 = @"Select nazwisko from user3

                          inner join samochod3

                          on userId = Id

                          where marka = 'FIAT'";

string s5 = @"Select nazwisko from user3, samochod3                        

                          where marka = 'FIAT' and userId = Id";

string s6 = @"Select nazwisko from user3 where id IN

                          (Select userId from Samochod3 where marka = 'FIAT')";

string s7 = @"Select VIN, userId from Samochod3";

SqlCommand cmd4 = new SqlCommand(s7, connection1);

SqlDataReader rd = cmd4.ExecuteReader();

SqlDataReader rd8; ;

while (rd.Read())

            {

if (CheckVin(Convert.ToInt32(rd[0])))

                {

string s8 = @"Select nazwisko from user3 where id = " + rd[1].ToString();

SqlCommand cmd8 = new SqlCommand(s8, connection1);

                    rd8 = cmd8.ExecuteReader();

if (rd8.HasRows)

while (rd8.Read())

                        listBox1.Items.Add(rd8[0].ToString());

                    rd8.Close();

                }

            }

            rd.Close();

            cmd4.ExecuteNonQuery();

        }

bool CheckVin(int VIN)

        {

if (VIN % 2 == 0)

return true;

else

return false;

        }

private void button2_Click(object sender, EventArgs e)

{

// string s4 = @"Update User3 set nazwisko = nazwisko + 'ski'";

string s4 = @"Update User3 set imie = Convert(char(10),imie) + 'ina'";

SqlCommand cmd4 = new SqlCommand(s4, connection1);

            cmd4.ExecuteNonQuery();

        }

private void button3_Click(object sender, EventArgs e)

        {

string s4 = @"Select * from User3";

SqlCommand cmd4 = new SqlCommand(s4, connection1);

SqlDataReader rd = cmd4.ExecuteReader();

while (rd.Read())

            {

string s = "";

for (int i = 0; i < rd.FieldCount; i++)

                    s += rd[i].ToString() + "  ";

                listBox1.Items.Add(s);

            }

            rd.Close();

        }

private void button6_Click(object sender, EventArgs e)

        {

// string s4 = @"Select * from BANK WHERE Id = " + textBox1.Text;

string s4 = @"Select * from BANK WHERE nazwisko = @id"; // +textBox1.Text;

SqlCommand cmd4 = new SqlCommand(s4, connection1);

            cmd4.Parameters.AddWithValue("@id", textBox1.Text);

SqlDataReader rd = cmd4.ExecuteReader();

while (rd.Read())

            {

string s = "";

for (int i = 0; i < rd.FieldCount; i++)

                    s += rd[i].ToString() + "  ";

                listBox1.Items.Add(s);

            }

            rd.Close();

        }

private void button7_Click(object sender, EventArgs e)

        {

//STORED PROCEDURE

string s = @"Create Procedure SelectBank

                         @id int,

                         @nazwisko nvarchar(20)

                         AS

                         Select * from BANK WHERE id = @id and nazwisko = @nazwisko";

SqlCommand cmd = new SqlCommand(s, connection1);

            cmd.ExecuteNonQuery();

        }

private void button8_Click(object sender, EventArgs e)

        {

string s4 = @"SelectBank ";

SqlCommand cmd4 = new SqlCommand(s4, connection1);

            cmd4.CommandType = CommandType.StoredProcedure;

            cmd4.Parameters.AddWithValue("@id", textBox1.Text);

cmd4.Parameters.AddWithValue("@nazwisko", "Sroka");

SqlDataReader rd = cmd4.ExecuteReader();

while (rd.Read())

            {

string s = "";

for (int i = 0; i < rd.FieldCount; i++)

                    s += rd[i].ToString() + "  ";

                listBox1.Items.Add(s);

            }

            rd.Close();

        }

    }

}

App.config

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

    <startup>

        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />

    </startup>

  <connectionStrings>

    <add name="con1" connectionString="Data Source=B325\SQL2012EXPRESS;Initial Catalog=test2;Integrated Security=True;MultipleActiveResultSets=True"/>

  </connectionStrings>

</configuration>