, , - , , . .
.
USE [Administraciya]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Otch_Zapis]
AS
BEGIN
SET NOCOUNT ON;
SELECT .[ ] as ,.,.,.,.[ ] as ,.,.,.,.,.
FROM []
LEFT JOIN ON
.[ID ]=.[ID ]
LEFT JOIN ON
.[ID ]=.[ID ]
LEFT JOIN ON
.[ID ]=.[ID ]
LEFT JOIN ON
.[ID ]=.[ID ]
GROUP BY .[ ],.,.,.,.[ ],.,.,.,.,.
END
( , )
USE [Administraciya]
GO
/****** Object: StoredProcedure [dbo].[Otch_Sotrudnik] Script Date: 04.07.2016 3:33:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Otch_Sotrudnik]
AS
BEGIN
SET NOCOUNT ON;
SELECT .[ID ] as ID, ,,, ., ., ,, .
FROM
LEFT JOIN ON
.[ID ]=.[ID ]
LEFT JOIN ON
.[ID ]=.[ID ]
GROUP BY .[ID ], ,,, ., ., ,, .
END
( , )
USE [Administraciya]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Otch_Ocheredniki]
AS
BEGIN
SET NOCOUNT ON;
SELECT .[ID ] as ID,,.,.,.,/*.[ ] as , .[ ] as */.
FROM
/*LEFT JOIN ON
.[ID ]=.[ID ]*/
LEFT JOIN ON
.[ID ]=.[ID ]
GROUP BY .[ID ],,.,.,.,/*.[ ], .[ ]*/.
|
|
END
( , )
, () . . , ; , .
:
1. , .
USE [Administraciya]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[Check_Double_Klient] ON [dbo].[]
FOR INSERT
AS
IF EXISTS (SELECT *
FROM , inserted
WHERE .=inserted. and .[ ]=inserted.[ ])
BEGIN
raiserror (' , !',16,1)
ROLLBACK tran
END
2.
USE Administraciya
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[Not_null_date_Group] ON [dbo].
AFTER INSERT NOT FOR REPLICATION AS
BEGIN
SET NOCOUNT ON;
DECLARE @ID int
DECLARE @Name nchar(20)
DECLARE @Opis nchar(200)
SELECT @ID=[ID ],@Name=,@Opis=[ ] from inserted
IF EXISTS (SELECT *
FROM
WHERE ='')
BEGIN
UPDATE SET =('!') WHERE [ID ] =@ID
END
IF EXISTS (SELECT *
FROM
WHERE [ ]='')
BEGIN
UPDATE SET [ ]=('!') WHERE [ID ] =@ID
END
END
GO
MS Visual Studio 2015.
Visual Studio :
1. Data Sources ( , Data Show Data Sources).
2. Add New Data Source Data Source Configuration Wizard Database . Next.
3. New Connection Add Connection . , Server name , , .
4. , Test Connection. . Add Connection Data Source Configuration Wizard . Next.
5. , Next . , . Finish.
|
|
Toolbox MenuStrip. : , , . : , . : , , . : , .
14. .
: , ,
15.
. .
Form1.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Collections;
using System.Data.SqlClient;
namespace WindowsFormsApplication1
{
public partial class Form1: Form
{
SqlConnection conn = null;
public Form1()
{
InitializeComponent();
}
private void ToolStripMenuItem_Click(object sender, EventArgs e)
{
}
private void ToolStripMenuItem_Click(object sender, EventArgs e)
{
this.Close();
}
private void ToolStripMenuItem_Click(object sender, EventArgs e)
{
}
private void Form1_Load(object sender, EventArgs e)
{
// TODO: "administraciyaDataSetZapis.". .
this.TableAdapter.Fill(this.administraciyaDataSetZapis.);
}
private void button1_Click(object sender, EventArgs e)
{
tabControl1.SelectedTab = tabPage2;
}
private void button2_Click(object sender, EventArgs e)
{
try
{
int ID = Convert.ToInt16(textBox1.Text);
int IDSotr = int.Parse(this.comboBox1.SelectedValue.ToString());
int IDOcher = int.Parse(this.comboBox2.SelectedValue.ToString());
int IDChelen = int.Parse(this.comboBox3.SelectedValue.ToString());
int IDGroup = int.Parse(this.comboBox4.SelectedValue.ToString());
string Primech = this.textBox6.Text;
conn = new SqlConnection();
conn.ConnectionString = "integrated security=SSPI;data source=\".\";persist security info=False; initial catalog=administraciya";//check
conn.Open();
SqlCommand myCommand = conn.CreateCommand();
myCommand.CommandText = "INSERT INTO [] ([ ],[ID ],[ID ],[ID ],[ID ],) VALUES (@ID,@IDSotr,@IDOcher,@IDChelen,@IDGroup,@Primech)";//check
myCommand.Parameters.Add("@ID", SqlDbType.Int, 4);
myCommand.Parameters["@ID"].Value = ID;
myCommand.Parameters.Add("@IDSotr", SqlDbType.Int, 4);
myCommand.Parameters["@IDSotr"].Value = IDSotr;
myCommand.Parameters.Add("@IDOcher", SqlDbType.Int, 4);
myCommand.Parameters["@IDOcher"].Value = IDOcher;
myCommand.Parameters.Add("@IDChelen", SqlDbType.Int, 4);
myCommand.Parameters["@IDChelen"].Value = IDChelen;
myCommand.Parameters.Add("@IDGroup", SqlDbType.Int, 4);
myCommand.Parameters["@IDGroup"].Value = IDGroup;
myCommand.Parameters.Add("@Primech", SqlDbType.NChar, 100);
myCommand.Parameters["@Primech"].Value = Primech;
int Uspeh = myCommand.ExecuteNonQuery();
if (Uspeh!= 0)
{
MessageBox.Show(" ");
}
else
{
MessageBox.Show(" ");
}
|
|
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
}
this.TableAdapter.Fill(this.administraciyaDataSetZapis.);
this.tabControl1.SelectedTab = tabPage1;
}
private void button3_Click(object sender, EventArgs e)
{
tabControl1.SelectedTab = tabPage1;
}
private void button4_Click(object sender, EventArgs e)
{
int IDZap = int.Parse(dataGridView1.Rows[dataGridView1.CurrentCell.RowIndex].Cells["DataGridViewTextBoxColumn"].Value.ToString());//check
DialogResult result = MessageBox.Show(" " + Convert.ToString(IDZap) + "?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2);
switch (result)
{
case DialogResult.Yes:
{
try
{
conn = new SqlConnection();
conn.ConnectionString = "integrated security=SSPI;data source=\".\";persist security info=False; initial catalog=Administraciya";//check
conn.Open();
SqlCommand myCommand = conn.CreateCommand();
myCommand.CommandText = "DELETE FROM [] WHERE [ ]=@IDZap";
myCommand.Parameters.Add("@IDZap", SqlDbType.Int, 4);
myCommand.Parameters["@IDZap"].Value = IDZap;
int Uspeh = myCommand.ExecuteNonQuery();
if (Uspeh!= 0)
{
MessageBox.Show(" ");
}
else
{
MessageBox.Show(" ");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
}
this.TableAdapter.Fill(this.administraciyaDataSetZapis.);
break;
}
}
}
private void dataGridView1_Click(object sender, EventArgs e)
{
try
{
int IDSotrud = int.Parse(dataGridView1.Rows[dataGridView1.CurrentCell.RowIndex].Cells["IDDataGridViewTextBoxColumn"].Value.ToString());
int IDOchered = int.Parse(dataGridView1.Rows[dataGridView1.CurrentCell.RowIndex].Cells["IDDataGridViewTextBoxColumn"].Value.ToString());
int IDOchelen = int.Parse(dataGridView1.Rows[dataGridView1.CurrentCell.RowIndex].Cells["IDDataGridViewTextBoxColumn"].Value.ToString());
int IDGroup = int.Parse(dataGridView1.Rows[dataGridView1.CurrentCell.RowIndex].Cells["IDDataGridViewTextBoxColumn"].Value.ToString());
conn = new SqlConnection();
conn.ConnectionString = "integrated security=SSPI;data source=\".\";persist security info=False; initial catalog=Administraciya";//check
SqlCommand myCommand = conn.CreateCommand();
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.CommandText = "[ID_to_Zapis]";
// ->
myCommand.Parameters.Add("@IDSotrud", SqlDbType.Int, 4);
myCommand.Parameters["@IDSotrud"].Value = IDSotrud;
myCommand.Parameters["@IDSotrud"].Direction = ParameterDirection.Input;
// ->
myCommand.Parameters.Add("@IDOchered", SqlDbType.Int, 4);
myCommand.Parameters["@IDOchered"].Value = IDOchered;
myCommand.Parameters["@IDOchered"].Direction = ParameterDirection.Input;
// ->
myCommand.Parameters.Add("@IDOchelen", SqlDbType.Int, 4);
myCommand.Parameters["@IDOchelen"].Value = IDOchelen;
myCommand.Parameters["@IDOchelen"].Direction = ParameterDirection.Input;
// ->
myCommand.Parameters.Add("@IDGroup", SqlDbType.Int, 4);
myCommand.Parameters["@IDGroup"].Value = IDGroup;
myCommand.Parameters["@IDGroup"].Direction = ParameterDirection.Input;
|
|
// <-
myCommand.Parameters.Add("@Sotrud_Name", SqlDbType.NChar, 100);
myCommand.Parameters["@Sotrud_Name"].Direction = ParameterDirection.Output;
// <-
myCommand.Parameters.Add("@Ochered_Name", SqlDbType.NChar, 100);
myCommand.Parameters["@Ochered_Name"].Direction = ParameterDirection.Output;
// <-
myCommand.Parameters.Add("@Chelen_name", SqlDbType.NChar, 100);
myCommand.Parameters["@Chelen_name"].Direction = ParameterDirection.Output;
// <-
myCommand.Parameters.Add("@Group_name", SqlDbType.NChar, 100);
myCommand.Parameters["@Group_name"].Direction = ParameterDirection.Output;
conn.Open();
myCommand.ExecuteScalar();
textBox3.Text = Convert.ToString(myCommand.Parameters["@Sotrud_Name"].Value);
textBox4.Text = Convert.ToString(myCommand.Parameters["@Ochered_Name"].Value);
textBox5.Text = Convert.ToString(myCommand.Parameters["@Chelen_name"].Value);
textBox7.Text = Convert.ToString(myCommand.Parameters["@Group_name"].Value);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
}
}
private void ToolStripMenuItem_Click(object sender, EventArgs e)
{
Form2 f2 = new Form2();
f2.Show();
}
private void ToolStripMenuItem_Click(object sender, EventArgs e)
{
Form3 f3 = new Form3();
f3.Show();
}
private void ToolStripMenuItem1_Click(object sender, EventArgs e)
{
Form4 f4 = new Form4();
f4.Show();
}
private void ToolStripMenuItem1_Click(object sender, EventArgs e)
{
Form5 f5 = new Form5();
f5.Show();
}
private void ToolStripMenuItem_Click(object sender, EventArgs e)
{
Form6 f6 = new Form6();
f6.Show();
}
private void ToolStripMenuItem_Click(object sender, EventArgs e)
{
Form7 f7 = new Form7();
f7.Show();
}
private void ToolStripMenuItem1_Click(object sender, EventArgs e)
{
Form8 f8 = new Form8();
f8.Show();
}
private void ToolStripMenuItem1_Click(object sender, EventArgs e)
{
Form9 f9 = new Form9();
f9.Show();
}
}
}
( . )
:
16. ,
17. ,
18. ,
19. ,
20. ,
TabControl. , .
, c TabPage1 TabPage2 .
private void button2_Click(object sender, EventArgs e)
{
tabControl1.SelectedTab = tabPage2;
}
( )
21, TabControl
TabPage2 TabPage1. (.26)
:
private void button2_Click(object sender, EventArgs e)
{
try
{
int ID = Convert.ToInt16(textBox1.Text);
int IDSotr = int.Parse(this.comboBox1.SelectedValue.ToString());
int IDOcher = int.Parse(this.comboBox2.SelectedValue.ToString());
int IDChelen = int.Parse(this.comboBox3.SelectedValue.ToString());
int IDGroup = int.Parse(this.comboBox4.SelectedValue.ToString());
string Primech = this.textBox6.Text;
conn = new SqlConnection();
conn.ConnectionString = "integrated security=SSPI;data source=\".\";persist security info=False; initial catalog=administraciya";//check
conn.Open();
SqlCommand myCommand = conn.CreateCommand();
myCommand.CommandText = "INSERT INTO [] ([ ],[ID ],[ID ],[ID ],[ID ],) VALUES (@ID,@IDSotr,@IDOcher,@IDChelen,@IDGroup,@Primech)";//check
myCommand.Parameters.Add("@ID", SqlDbType.Int, 4);
myCommand.Parameters["@ID"].Value = ID;
myCommand.Parameters.Add("@IDSotr", SqlDbType.Int, 4);
|
|
myCommand.Parameters["@IDSotr"].Value = IDSotr;
myCommand.Parameters.Add("@IDOcher", SqlDbType.Int, 4);
myCommand.Parameters["@IDOcher"].Value = IDOcher;
myCommand.Parameters.Add("@IDChelen", SqlDbType.Int, 4);
myCommand.Parameters["@IDChelen"].Value = IDChelen;
myCommand.Parameters.Add("@IDGroup", SqlDbType.Int, 4);
myCommand.Parameters["@IDGroup"].Value = IDGroup;
myCommand.Parameters.Add("@Primech", SqlDbType.NChar, 100);
myCommand.Parameters["@Primech"].Value = Primech;
int Uspeh = myCommand.ExecuteNonQuery();
if (Uspeh!= 0)
{
MessageBox.Show(" ");
}
else
{
MessageBox.Show(" ");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
}
this.TableAdapter.Fill(this.administraciyaDataSetZapis.);
this.tabControl1.SelectedTab = tabPage1;
}
( )
private void button4_Click(object sender, EventArgs e)
{ tabControl1.SelectedTab = tabPage1; }
( )
private void button4_Click(object sender, EventArgs e)
{
int IDZap = int.Parse(dataGridView1.Rows[dataGridView1.CurrentCell.RowIndex].Cells["DataGridViewTextBoxColumn"].Value.ToString());//check
DialogResult result = MessageBox.Show(" " + Convert.ToString(IDZap) + "?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2);
switch (result)
{
case DialogResult.Yes:
{
try
{
conn = new SqlConnection();
conn.ConnectionString = "integrated security=SSPI;data source=\".\";persist security info=False; initial catalog=Administraciya";//check
conn.Open();
SqlCommand myCommand = conn.CreateCommand();
myCommand.CommandText = "DELETE FROM [] WHERE [ ]=@IDZap";
myCommand.Parameters.Add("@IDZap", SqlDbType.Int, 4);
myCommand.Parameters["@IDZap"].Value = IDZap;
int Uspeh = myCommand.ExecuteNonQuery();
if (Uspeh!= 0)
{
MessageBox.Show(" ");
}
else
{
MessageBox.Show(" ");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
}
this.TableAdapter.Fill(this.administraciyaDataSetZapis.);
break;
}
}
}
( )
. Windows Visual C# ReportViewer . , , ADO.NET. API , , .
3 :
1. .
22. ,
2. .
23. ,
3. .
24. ,
, Visual Studio C# Sql Server. . , .
, , . , .
, ********
-, . , . .
, . , . . . .