[PCS] cvičení

[Destroyer]

cvičení 1

DBDataReader.zip

(58.01 KiB) Staženo 988 x

northwind.zip

(636.9 KiB) Staženo 1006 x

[Destroyer]

cviceni 2

Kód: Vybrat vše

-- =============================================
-- Author:		<Author,,Name>
-- Create date: 26.5.2015
-- Description:	Prvni run je potreba zmenit radek 6 na CREATE PROCEDURE
-- =============================================
ALTER PROCEDURE ProductsByUnits
    @count int OUTPUT,
	@min int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	Select @count = Count(*) FROM Products WHERE UnitsInStock > @min  -- priradi hodnotu do vystupniho parametru
	SELECT UnitsInStock,ProductName FROM Products WHERE UnitsInStock > @min ORDER BY UnitsInStock DESC, ProductName
END
GO
-- spoustet oddelene
Declare @pocet int

exec ProductsByUnits @pocet OUTPUT, 110
Select @pocet as Pocet

[Destroyer]

Kód: Vybrat vše

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.Data.SqlClient;
namespace DBDataReader
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        SqlConnection conn;
        SqlCommand cmdSelect;
        SqlDataReader reader;
        SqlCommand cmdUpdate;
        //cviceni 2
        SqlCommand cmdSP;
        SqlParameter parMin, parCount;

        int ID, Krestni, Prijmeni, Osloveni, Mesto;

        private void btnText_Click(object sender, EventArgs e)
        {
            FillForm();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            conn = new SqlConnection();
            // v praxi nevhodne, lepsi vlozit do konfiguracniho souboru
            // connection string pro SQL server security 
            conn.ConnectionString = "User ID=sa;Password=Pa$$w0rd;Data Source=MIREK-THINKPAD\\SQLEXPRESS;Initial Catalog=Northwind";
            // string pro Windows security (Integrated security, Trusted connection ...)
            //conn.ConnectionString = "User Integrated Security=yes;Data Source=localhost;Initial Catalog=Northwind";
            // connection string pro LocalDB 
            //conn.ConnectionString = "User Integrated Security=yes;Data Source=(LocalDB)\\MSSQLSLocalDB;Initial Catalog=Northwind";
            bool bad = false;

            // try/catch pouzit u VSECH operaci s DB
            try { conn.Open(); }
            catch (SqlException ex)
            {
                MessageBox.Show("ADO err: " + ex.Message);
                foreach (SqlError err in ex.Errors)
                {
                    MessageBox.Show("SQL err: " + err.Message);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                bad = true;
            }

            if (bad) return;


            cmdSelect = new SqlCommand();
            cmdSelect.Connection = conn;
            cmdSelect.CommandText = "Select EmployeeID,LastName,FirstName,TitleOfCourtesy,City From Employees";

            cmdUpdate = new SqlCommand();
            cmdUpdate.Connection = conn;

            // priprava volani stored procedury (cv2)
            cmdSP = new SqlCommand();
            cmdSP.Connection = conn;
            cmdSP.CommandType = CommandType.StoredProcedure;
            cmdSP.CommandText = "ProductsByUnits"; //jmeno stored procedury

            // priprava parametru
            parMin = new SqlParameter();
            parMin.ParameterName = "@min"; // jmeno se MUSI shodovat se jmenem v SP
            parMin.SqlDbType = SqlDbType.Int;
            parMin.Direction = ParameterDirection.Input;
            cmdSP.Parameters.Add(parMin); // pridani do kolekce parametru prikazu - NUTNE

            // priprava parametru vystupniho
            parCount = new SqlParameter();
            parCount.ParameterName = "@count"; // jmeno se MUSI shodovat se jmenem v SP
            parCount.SqlDbType = SqlDbType.Int;
            parCount.Direction = ParameterDirection.Output;
            cmdSP.Parameters.Add(parCount); // pridani do kolekce parametru prikazu - NUTNE

            // datareader je objekt, ktery reprezentuje (zpristupnuje) nacitana data
            reader = cmdSelect.ExecuteReader(); // posle na DB command, ktery vraci data

            ID = reader.GetOrdinal("EmployeeID");
            Krestni = reader.GetOrdinal("FirstName");
            Prijmeni = reader.GetOrdinal("LastName");
            Osloveni = reader.GetOrdinal("TitleOfCourtesy");
            Mesto = reader.GetOrdinal("City");
            FillForm();

        }
        void FillForm()
        {
            // na zacatku stoji DataReader PRED prvnim recordem, metoda Read zpristupni dalsi record
            if ( reader.Read() ) // Read vraci false, pokud jiz dalsi record neexistuje
            {
                // varianta 1 - efektivni ale neprehledna 
                /*
                txtID.Text = reader.GetInt32(0).ToString(); // 0 cislo sloupce v selectu
                txtKrestni.Text = reader.GetString(2);
                txtPrijmeni.Text = reader.GetString(1);
                txtOsloveni.Text = reader.GetString(3);
                txtMesto.Text = reader.GetString(4);
                */
                // varianta 2 - neefektivni, prehledna
                /*txtID.Text = reader["EmployeeID"].ToString(); // 0 cislo sloupce v selectu
                txtKrestni.Text = reader["FirstName"].ToString();
                txtPrijmeni.Text = reader.["LastName"].ToString();
                txtOsloveni.Text = reader.["TitleOfCourtesy"].ToString();
                txtMesto.Text = reader.["City"].ToString();
                 * */
                // varianta 3 - efektivni, prehledna ale pozor na NULL
                /*txtID.Text = reader.GetInt32(ID).ToString();
                txtKrestni.Text = reader.GetString(Krestni);
                txtPrijmeni.Text = reader.GetString(Prijmeni);
                txtOsloveni.Text = reader.GetString(Osloveni);
                txtMesto.Text = reader.GetString(Mesto);*/

                // prace s NULL hodnotami
                txtID.Text = reader[ID].ToString(); // 0 cislo sloupce v selectu
                txtKrestni.Text = reader[Krestni].ToString();
                txtPrijmeni.Text = reader[Prijmeni].ToString();
                txtOsloveni.Text = reader[Osloveni].ToString();
                txtMesto.Text = reader[Mesto].ToString();

            }
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (reader != null) reader.Close();
            if (conn.State == ConnectionState.Open) conn.Close();
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            string strSql;
            strSql = "Update Employees Set LastName='" + txtPrijmeni.Text + "' Where EmployeeID=" + txtID.Text;

            txtSql.Text = strSql; // debug only

            cmdUpdate.CommandText = strSql;

            // po connection s OTEVRENYM data readerem  NELZE poslat dalsi SQL command
            reader.Close();

            cmdUpdate.ExecuteNonQuery(); // posila na DB command, ktery NEVRACI data (insert,update,create table...) 

            reader = cmdSelect.ExecuteReader();
            FillForm();


            // cv2
            //int pocet = cmdUpdate.ExecuteNonQuery(); // vrati pocet updatovanych, deletovanych 
            //int cislo = (int)cmdSelect.ExecuteScalar(); // vrati jednu hodnotu

            /*do
            {
                while (reader.Read()) // nacte data pouze z 1 selectu
                {

                }
            }
            while (reader.NextResult()); // NextResult - zacni cist data z dalsiho selectu (resultsetu), vrati false nejsou-li dalsi data*/
        }

        private void btnSP_Click(object sender, EventArgs e)
        {
            txtSql.Clear();
            parMin.Value = int.Parse(txtMin.Text);
           // parMin.Value = Convert.ToInt32(txtMin.Text);
            reader.Close();
            reader = cmdSP.ExecuteReader();
            // zde NELZE pouzit VYSTUPNI PARAMETRU-->
            //txtSql.Text += "\r\nPocet radku= " + parCount.Value + "\r\n\r\n";
            while (reader.Read())
            {
                txtSql.Text += reader["UnitsInStock"] + "; " + reader["ProductName"] + "\r\n";
            }
            reader.Close();

            // --->lze pouzit az zde , az po uzavreni DataReaderu
            txtSql.Text += "\r\nPocet radku= " + parCount.Value + "\r\n\r\n";

            reader = cmdSelect.ExecuteReader();
            FillForm();
        }
    }
}

Kód: Vybrat vše

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.Data.OleDb;
namespace DBDataReaderOLEDB
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        OleDbConnection conn;
        OleDbCommand cmdSelect;
        OleDbDataReader reader;
        OleDbCommand cmdUpdate;
        //cviceni 2
        OleDbCommand cmdSP;
        OleDbParameter parMin, parCount;

        int ID, Krestni, Prijmeni, Osloveni, Mesto;

        private void btnText_Click(object sender, EventArgs e)
        {
            FillForm();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            conn = new OleDbConnection();
            // v praxi nevhodne, lepsi vlozit do konfiguracniho souboru
            // musi byt nainstalovat access driver
            conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=northwind.mdb";
            bool bad = false;

            // try/catch pouzit u VSECH operaci s DB
            try { conn.Open(); }
            catch (OleDbException ex)
            {
                MessageBox.Show("ADO err: " + ex.Message);
                foreach (OleDbError err in ex.Errors)
                {
                    MessageBox.Show("SQL err: " + err.Message);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                bad = true;
            }

            if (bad) return;


            cmdSelect = new OleDbCommand();
            cmdSelect.Connection = conn;
            cmdSelect.CommandText = "Select EmployeeID,LastName,FirstName,TitleOfCourtesy,City From Employees";

            cmdUpdate = new OleDbCommand();
            cmdUpdate.Connection = conn;

            // priprava volani stored procedury (cv2)
            cmdSP = new OleDbCommand();
            cmdSP.Connection = conn;
            cmdSP.CommandType = CommandType.StoredProcedure;
            cmdSP.CommandText = "ProductsByUnits"; //jmeno stored procedury

            // priprava parametru
            parMin = new OleDbParameter();
            parMin.ParameterName = "@min"; // jmeno se MUSI shodovat se jmenem v SP
            parMin.OleDbType = OleDbType.Integer;
            parMin.Direction = ParameterDirection.Input;
            cmdSP.Parameters.Add(parMin); // pridani do kolekce parametru prikazu - NUTNE

            // priprava parametru vystupniho
            parCount = new OleDbParameter();
            parCount.ParameterName = "@count"; // jmeno se MUSI shodovat se jmenem v SP
            parCount.OleDbType = OleDbType.Integer;
            parCount.Direction = ParameterDirection.Output;
            cmdSP.Parameters.Add(parCount); // pridani do kolekce parametru prikazu - NUTNE

            // datareader je objekt, ktery reprezentuje (zpristupnuje) nacitana data
            reader = cmdSelect.ExecuteReader(); // posle na DB command, ktery vraci data

            ID = reader.GetOrdinal("EmployeeID");
            Krestni = reader.GetOrdinal("FirstName");
            Prijmeni = reader.GetOrdinal("LastName");
            Osloveni = reader.GetOrdinal("TitleOfCourtesy");
            Mesto = reader.GetOrdinal("City");
            FillForm();

        }
        void FillForm()
        {
            // na zacatku stoji DataReader PRED prvnim recordem, metoda Read zpristupni dalsi record
            if ( reader.Read() ) // Read vraci false, pokud jiz dalsi record neexistuje
            {
                // varianta 1 - efektivni ale neprehledna 
                /*
                txtID.Text = reader.GetInt32(0).ToString(); // 0 cislo sloupce v selectu
                txtKrestni.Text = reader.GetString(2);
                txtPrijmeni.Text = reader.GetString(1);
                txtOsloveni.Text = reader.GetString(3);
                txtMesto.Text = reader.GetString(4);
                */
                // varianta 2 - neefektivni, prehledna
                /*txtID.Text = reader["EmployeeID"].ToString(); // 0 cislo sloupce v selectu
                txtKrestni.Text = reader["FirstName"].ToString();
                txtPrijmeni.Text = reader.["LastName"].ToString();
                txtOsloveni.Text = reader.["TitleOfCourtesy"].ToString();
                txtMesto.Text = reader.["City"].ToString();
                 * */
                // varianta 3 - efektivni, prehledna ale pozor na NULL
                /*txtID.Text = reader.GetInt32(ID).ToString();
                txtKrestni.Text = reader.GetString(Krestni);
                txtPrijmeni.Text = reader.GetString(Prijmeni);
                txtOsloveni.Text = reader.GetString(Osloveni);
                txtMesto.Text = reader.GetString(Mesto);*/

                // prace s NULL hodnotami
                txtID.Text = reader[ID].ToString(); // 0 cislo sloupce v selectu
                txtKrestni.Text = reader[Krestni].ToString();
                txtPrijmeni.Text = reader[Prijmeni].ToString();
                txtOsloveni.Text = reader[Osloveni].ToString();
                txtMesto.Text = reader[Mesto].ToString();

            }
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (reader != null) reader.Close();
            if (conn.State == ConnectionState.Open) conn.Close();
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            string strSql;
            strSql = "Update Employees Set LastName='" + txtPrijmeni.Text + "' Where EmployeeID=" + txtID.Text;

            txtSql.Text = strSql; // debug only

            cmdUpdate.CommandText = strSql;

            // po connection s OTEVRENYM data readerem  NELZE poslat dalsi SQL command
            reader.Close();

            cmdUpdate.ExecuteNonQuery(); // posila na DB command, ktery NEVRACI data (insert,update,create table...) 

            reader = cmdSelect.ExecuteReader();
            FillForm();


            // cv2
            //int pocet = cmdUpdate.ExecuteNonQuery(); // vrati pocet updatovanych, deletovanych 
            //int cislo = (int)cmdSelect.ExecuteScalar(); // vrati jednu hodnotu

            /*do
            {
                while (reader.Read()) // nacte data pouze z 1 selectu
                {

                }
            }
            while (reader.NextResult()); // NextResult - zacni cist data z dalsiho selectu (resultsetu), vrati false nejsou-li dalsi data*/
        }

    }
}

Kód: Vybrat vše

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.Data.SqlClient;
namespace DBTransactions
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        SqlConnection conn;
        SqlCommand cmdSelect;
        SqlDataReader reader;
        SqlCommand cmdUpdate;
        // cv 2
        SqlTransaction tran;

        int ID, Krestni, Prijmeni, Osloveni, Mesto;


        private void Form1_Load(object sender, EventArgs e)
        {
            conn = new SqlConnection();
            conn.ConnectionString = "User ID=sa;Password=Pa$$w0rd;Data Source=MIREK-THINKPAD\\SQLEXPRESS;Initial Catalog=Northwind";
            conn.Open();

            // zahaji transakci a vrati objekt typu SqlTransaction, kterym ridime dalsi chovani transakce
            tran = conn.BeginTransaction(IsolationLevel.ReadUncommitted);
            // nikdy neposilat prikazy commit tran/rollback tran , pokud jsme zahajili transakci metodou begin transaction. Potom ukoncit transakci volanim prislusnych metod objektu transakce - .Commit nebo .Rollback

            cmdSelect = new SqlCommand();
            cmdSelect.Connection = conn;
            cmdSelect.CommandText = "Select EmployeeID,LastName,FirstName,TitleOfCourtesy,City From Employees";
            cmdSelect.Transaction = tran; // vsechny commandy posilane po connection se zahajenou transakci musi mit nastavenou vlastnost transaction

            cmdUpdate = new SqlCommand();
            cmdUpdate.Connection = conn;
            cmdUpdate.Transaction = tran;

            // datareader je objekt, ktery reprezentuje (zpristupnuje) nacitana data
            reader = cmdSelect.ExecuteReader(); // posle na DB command, ktery vraci data

            ID = reader.GetOrdinal("EmployeeID");
            Krestni = reader.GetOrdinal("FirstName");
            Prijmeni = reader.GetOrdinal("LastName");
            Osloveni = reader.GetOrdinal("TitleOfCourtesy");
            Mesto = reader.GetOrdinal("City");
            FillForm();

        }
        void FillForm()
        {
            // na zacatku stoji DataReader PRED prvnim recordem, metoda Read zpristupni dalsi record
            if ( reader.Read() ) // Read vraci false, pokud jiz dalsi record neexistuje
            {
                // prace s NULL hodnotami
                txtID.Text = reader[ID].ToString(); // 0 cislo sloupce v selectu
                txtKrestni.Text = reader[Krestni].ToString();
                txtPrijmeni.Text = reader[Prijmeni].ToString();
                txtOsloveni.Text = reader[Osloveni].ToString();
                txtMesto.Text = reader[Mesto].ToString();

            }
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (reader != null) reader.Close();
            if (conn.State == ConnectionState.Open) conn.Close();
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            string strSql;
            strSql = "Update Employees Set LastName='" + txtPrijmeni.Text + "' Where EmployeeID=" + txtID.Text;

           // txtSql.Text = strSql; // debug only

            cmdUpdate.CommandText = strSql;

            // po connection s OTEVRENYM data readerem  NELZE poslat dalsi SQL command
            reader.Close();

            cmdUpdate.ExecuteNonQuery(); // posila na DB command, ktery NEVRACI data (insert,update,create table...) 

            reader = cmdSelect.ExecuteReader();
            FillForm();
        }
        private void btnText_Click(object sender, EventArgs e)
        {
            FillForm();
        }

        private void btnRewind_Click(object sender, EventArgs e)
        {
            reader.Close();
            reader = cmdSelect.ExecuteReader();
            FillForm();
        }

        private void btnCommit_Click(object sender, EventArgs e)
        {
            reader.Close(); // jen abych mohl poslat commit
            tran.Commit();
            reader = cmdSelect.ExecuteReader();
            FillForm();
        }

        private void btnRollback_Click(object sender, EventArgs e)
        {
            reader.Close(); // jen abych mohl poslat commit
            tran.Rollback();
            reader = cmdSelect.ExecuteReader();
            FillForm();
        }

    }
}

Kód: Vybrat vše

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.Data.SqlClient;
namespace DBDataSet
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        SqlConnection conn;
        SqlCommand cmdSelect;
        // cv3
        SqlDataAdapter adapter;
        DataSet ds;
        DataView view;
        SqlCommand cmdUpdate, cmdDelete, cmdInsert;
        
        int CurrentRow;
        bool changed = false;

        private void Form1_Load(object sender, EventArgs e)
        {
            conn = new SqlConnection();
            conn.ConnectionString = "User ID=sa;Password=Pa$$w0rd;Data Source=MIREK-THINKPAD\\SQLEXPRESS;Initial Catalog=Northwind";
    
            cmdSelect = new SqlCommand();
            cmdSelect.Connection = conn;
            cmdSelect.CommandText = "Select EmployeeID,LastName,FirstName,TitleOfCourtesy,City,TS From Employees";

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = cmdSelect;

            // automaticke vytvoreni SQLCommandu pro DataAdapter
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
            txtSql.Text = builder.GetUpdateCommand().CommandText;



            // priprava SQL comandu pro DataAdapter
            // UPDATE command
            cmdUpdate = new SqlCommand();
            cmdUpdate.Connection = conn;
            cmdUpdate.CommandText = "Update Employees Set LastName=@LastName,FirstName=@FirstName,TitleofCourtesy=@TitleOfCourtesy,City=@City Where EmployeeID=@EmployeeID and TS=@TS";
            
            cmdUpdate.Parameters.Add("@LastName", SqlDbType.NVarChar, 20, "LastName");
            cmdUpdate.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10, "FirstName");
            cmdUpdate.Parameters.Add("@TitleOfCourtesy", SqlDbType.NVarChar, 25, "TitleOfCourtesy");
            cmdUpdate.Parameters.Add("@City", SqlDbType.NVarChar, 15, "City");
            SqlParameter paraUpdateID = cmdUpdate.Parameters.Add("@EmployeeID", SqlDbType.Int, 4, "EmployeeID");
            paraUpdateID.SourceVersion = DataRowVersion.Original; // vzit puvodni hodnotu
            SqlParameter parUpdateTS = cmdUpdate.Parameters.Add("@TS", SqlDbType.Timestamp, 8, "TS"); //timestamp, - noveji rowversion
            parUpdateTS.SourceVersion = DataRowVersion.Original; // vzit puvodni hodnotu (zde neni nutne - timestamp je read only)

            adapter.UpdateCommand = cmdUpdate;

            // INSERT command
            cmdInsert = new SqlCommand();
            cmdInsert.Connection = conn;
            cmdInsert.CommandText = "Insert Into Employees (LastName,FirstName,City,TitleofCourtesy) Values (@LastName, @FirstName, @TitleOfCourtesy,@City)";

            cmdInsert.Parameters.Add("@LastName", SqlDbType.NVarChar, 20, "LastName");
            cmdInsert.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10, "FirstName");
            cmdInsert.Parameters.Add("@TitleOfCourtesy", SqlDbType.NVarChar, 25, "TitleOfCourtesy");
            cmdInsert.Parameters.Add("@City", SqlDbType.NVarChar, 15, "City");
            
            adapter.InsertCommand = cmdInsert;

            // DELETE command
            cmdDelete = new SqlCommand();
            cmdDelete.Connection = conn;
            cmdDelete.CommandText = "Delete From Employees Where EmployeeID=@EmployeeID";


            SqlParameter paraDeleteID = cmdDelete.Parameters.Add("@EmployeeID", SqlDbType.Int, 4, "EmployeeID");
            paraUpdateID.SourceVersion = DataRowVersion.Original; // vzit puvodni hodnotu
            adapter.DeleteCommand = cmdDelete;


            ds = new DataSet();
            
            // data adapter po otevrenem connection posle prislusny select a necha connection otevrenou
            // pro zavrene connection jej otevre, posle select a zavre( zustane v puvodnim nastaveni)
            conn.Open(); // neni treba?!
            // .Fill nacte do tabulky v Datasetu data podle commandu cmdSelect 
            // pokus v ds tabulka neexistuje, DataAdapter ji vytvori
            adapter.Fill(ds, "memEmployees");
            conn.Close(); // neni treba?!

            //view = ds.Tables["memEmployees"].DefaultView; // pouziti implicitniho view
            view = new DataView(ds.Tables["memEmployees"]);

            cboOsloveni.SelectedIndex = 0;

            FillForm(CurrentRow = 0);

        }
        void FillForm(int index)
        {
            // varianta 1 - prime pouziti DataTable
            /*DataRow dr;
            // v praxi otestovat, zda je 0 <= index < ds.Tables["memEmployees"].Rows.Count
            dr = ds.Tables["memEmployees"].Rows[index];
                txtID.Text = dr["EmployeeID"].ToString(); // 0 cislo sloupce v selectu
                txtKrestni.Text = dr["FirstName"].ToString();
                txtPrijmeni.Text = dr["LastName"].ToString();
                txtOsloveni.Text = dr["TitleOfCourtesy"].ToString();
                txtMesto.Text = dr["City"].ToString();

                txtRow.Text = (index + 1).ToString();
                txtCount.Text = ds.Tables["memEmployees"].Rows.Count.ToString();
            */

            // varianta 2 - dataView
            DataRowView drv;
            // v praxi otestovat zda je 0 <= index < view.Count
            drv = view[index];
            txtID.Text = drv["EmployeeID"].ToString(); // 0 cislo sloupce v selectu
            txtKrestni.Text = drv["FirstName"].ToString();
            txtPrijmeni.Text = drv["LastName"].ToString();
            txtOsloveni.Text = drv["TitleOfCourtesy"].ToString();
            txtMesto.Text = drv["City"].ToString();

            txtRow.Text = (index + 1).ToString();
            txtCount.Text = view.Count.ToString();
            changed = false;
        }


        private void btnFirst_Click(object sender, EventArgs e)
        {
            UpdateData(true);
            FillForm(CurrentRow = 0);
        }

        private void btnPrev_Click(object sender, EventArgs e)
        {
            UpdateData(true);
            if (CurrentRow > 0)
                FillForm(--CurrentRow);
        }

        private void btnNext_Click(object sender, EventArgs e)
        {
            UpdateData(true);
            if (CurrentRow < view.Count -1)
                FillForm(++CurrentRow);
        }

        private void btnLast_Click(object sender, EventArgs e)
        {
            UpdateData(true);
            FillForm(CurrentRow = view.Count - 1);
        }

        private void cboOsloveni_SelectedIndexChanged(object sender, EventArgs e)
        {
            UpdateData(true);
            string strFilter = ""; // nefiltrovat
            if (cboOsloveni.SelectedItem != "All")
            {
                strFilter = "TitleOfCourtesy = '" + cboOsloveni.SelectedItem + "'"; // jako klauzule where (bez klicoveho slova)
            }

            view.RowFilter = strFilter;
            // v praxi otestovat zda po filtrovani zbyl alespon 1 radek
            FillForm(CurrentRow = 0);
        }

        private void btnSort_CheckedChanged(object sender, EventArgs e)
        {
            UpdateData(true);
            string strSort = ""; // nesortovat (zrusit sortovani)
            if (chkSort.Checked) 
                strSort = "LastName DESC"; // jako order by
            view.Sort = strSort;
            FillForm(CurrentRow = 0);
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {

            UpdateData(false);
        }
        void UpdateData(bool ask)
        {
            DataRowView drv;

            if (!changed) return;
            if (ask)
            {
                DialogResult volba;
                volba = MessageBox.Show("Data zmenena. Ulozit?","Varovani",MessageBoxButtons.YesNo,MessageBoxIcon.Warning,MessageBoxDefaultButton.Button1);
                if (volba == DialogResult.No)
                { 
                    changed = false; // dle situace
                    return;
                }
            }
            // otestovat currentRow
            drv = view[CurrentRow];
            drv.BeginEdit();
            drv["FirstName"] = txtKrestni.Text;
            drv["LastName"] = txtPrijmeni.Text;
            drv["TitleOfCourtesy"] = txtOsloveni.Text;
            drv["City"] = txtMesto.Text;
            drv.EndEdit();
            //drv.CancelEdit(); zruseni editace
            changed = true;
        }

        private void TextBoxes_TextChanged(object sender, EventArgs e)
        {
            changed = true;
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            if ( changed )
            {
                MessageBox.Show("Form nelze uzavrit, zmeny nejsou ulozeny");
                e.Cancel = true;
            }
        }

        private void btnClear_Click(object sender, EventArgs e)
        {
            txtID.Clear();
            txtKrestni.Clear();
            txtPrijmeni.Clear();
            txtOsloveni.Clear();
            txtMesto.Clear();
        }

        private void btnInsert_Click(object sender, EventArgs e)
        {
            // vkladani dat primo do tabulky
            /*
            DataRow dr;
            dr = ds.Tables["memEmployees"].NewRow(); // vytvori novy, spravne strukturovany radek

            dr.BeginEdit(); // .Begin/EndEdit nutne
            dr["FirstName"] = txtKrestni.Text;
            dr["LastName"] = txtPrijmeni.Text;
            dr["TitleOfCourtesy"] = txtOsloveni.Text;
            dr["City"] = txtMesto.Text;
            dr.EndEdit();

            ds.Tables["memEmployees"].Rows.Add(dr); // pridani do kolekce radku tabulky

            FillForm(CurrentRow = ds.Tables["memEmployees"].Rows.Count - 1); // prejit na posledni radek
             * */
            // vkladani data prostrednictvim DataView
            DataRowView drv;
            drv = view.AddNew(); // vytvori novy, spravne strukturovany radek a pripoji ho do tabulky

            drv.BeginEdit(); // .Begin/EndEdit nutne
            drv["FirstName"] = txtKrestni.Text;
            drv["LastName"] = txtPrijmeni.Text;
            drv["TitleOfCourtesy"] = txtOsloveni.Text;
            drv["City"] = txtMesto.Text;
            drv.EndEdit();

            // jen, neni-li sort nebo filtrovani
            FillForm(CurrentRow = view.Count - 1); // prejit na posledni radek
            
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            DataRowView drv;
            drv = view[CurrentRow];
            drv.Delete();
            if (CurrentRow >= view.Count)
                CurrentRow = view.Count - 1;
            FillForm(CurrentRow);

            // pri vymazu v tabulce(nebo view) o jedinem radku vymazat textBoxy, zablokovat tlacitka krome insertu a napr. zruseni pripadneho filtrovani (view.Count == 0)
            // pri vymmazu pomoci DataView se deletovane radky odfiltrovavaji
            // pri pohybu primo po tabulce je treba tento priznak testovat  
        }

        private void btnSaveToDB_Click(object sender, EventArgs e)
        {
            // varianta 1 - ulozeni dat ridi DataAdapter
            //adapter.Update(ds, "memEmployees");

            // varianta 2 - ulozeni dat ridi program
            /*DataRow[] radky;
            radky = ds.Tables["memEmployees"].Select(null, null, DataViewRowState.Deleted);
            adapter.Update(radky);
            radky = ds.Tables["memEmployees"].Select(null, null, DataViewRowState.ModifiedCurrent);
            adapter.Update(radky);
            radky = ds.Tables["memEmployees"].Select(null, null, DataViewRowState.Added);
            adapter.Update(radky);*/

            // varianta 3 - reseni pomoci DBConcurrencyException
            /*conn.Open();
            SqlTransaction tran = conn.BeginTransaction();
            adapter.UpdateCommand.Transaction = tran;
            adapter.InsertCommand.Transaction = tran;
            adapter.DeleteCommand.Transaction = tran;
            try
            {
                adapter.Update(ds, "memEmployees");
            }
            catch (DBConcurrencyException ex)
            {
                tran.Rollback();
                txtSql.Text = "Kolize: EmployeeID = " + ex.Row["EmployeeID"] + "; LastName=" + ex.Row["LastName"] + "\r\n";
            }
            catch (Exception ex)
            {
                txtSql.Text = ex.Message;
            }*/

            // varianta 4 - reseni kolize bez pomoci DBConcurrencyException
            adapter.ContinueUpdateOnError = true;
            adapter.Update(ds, "memEmployees");

            if (ds.Tables["memEmployees"].HasErrors)
            {
                txtSql.Clear();
                foreach (DataRow radek in ds.Tables["memEmployees"].GetErrors())
                {
                    txtSql.Text += "Kolize " + radek["EmployeeID"] + ": " + radek["LastName"] + "\r\n";               
                }
            }
        }
        void adapter_RowUpdating(object sender, SqlRowUpdatedEventArgs e)
        {
            string zprava = "Radek";
            if (e.StatementType != StatementType.Delete)
            {
                zprava += e.StatementType.ToString() + "; " + e.Row["EmployeeID"] + "; " + e.Row["LastName"];
            }
            else
            {
                zprava += e.StatementType.ToString() + "; " + e.Row["EmployeeID", DataRowVersion.Original] + "; " + e.Row["LastName", DataRowVersion.Original]; // puvodni nesmazana data
            }
            //MessageBox.Show(zprava);
        }
    }
}

Kód: Vybrat vše

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.Data.SqlClient;
namespace DSetDB
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        DataSet ds;
        DataTable tbl;
        DataColumn col;
        DataRow dr;

        int currentRow = 0;

        private void Form1_Load(object sender, EventArgs e)
        {
            ds = new DataSet();
            tbl = new DataTable();
            tbl.TableName = "Lide";
            ds.Tables.Add(tbl);

            col = new DataColumn();
            col.ColumnName = "ID";
            col.AutoIncrement = true;
            col.AutoIncrementSeed = 0;
            col.AutoIncrementStep = 1;
            col.DataType = typeof(int);
            tbl.Columns.Add(col);

            //definice primarniho klice
            tbl.PrimaryKey = new DataColumn[] { tbl.Columns["ID"] };

            // druha varianta pridani sloupce - kompaktneji
            col = tbl.Columns.Add("jmeno", typeof(string));
            col.MaxLength = 50;

            // naplneni dat
            dr = tbl.NewRow();
            dr.BeginEdit();
            dr["ID"] = 1;
            dr["Jmeno"] = "Prvni";
            dr.EndEdit();
            tbl.Rows.Add(dr);

            dr = tbl.NewRow();
            dr.BeginEdit();
            dr["ID"] = 2;
            dr["Jmeno"] = "Druhy";
            dr.EndEdit();
            tbl.Rows.Add(dr);

            dr = tbl.NewRow();
            dr.BeginEdit();
            dr["ID"] = 3;
            dr["Jmeno"] = "Treti";
            dr.EndEdit();
            tbl.Rows.Add(dr);

            FillForm(currentRow);
        }
        
        void FillForm(int index)
        {
            DataRow dr = tbl.Rows[index];
            txtID.Text = dr["ID"].ToString();
            txtJmeno.Text = dr["Jmeno"].ToString();
        
        }

        private void btnEditID_Click(object sender, EventArgs e)
        {
            DataRow dr = tbl.Rows[currentRow];
            // editaci lze zakazat na sloupci nastavenim .ReadOnly = true
            dr["ID"] = int.Parse(txtID.Text);
        }

        private void btnInsert_Click(object sender, EventArgs e)
        {
            DataRow dr;

            dr = tbl.NewRow();
            dr.BeginEdit();
            dr["Jmeno"] = txtJmeno.Text;
            dr.EndEdit();
            tbl.Rows.Add(dr);

            FillForm(currentRow = tbl.Rows.Count - 1);
        }

        private void btnNext_Click(object sender, EventArgs e)
        {
            if (currentRow < tbl.Rows.Count -1) FillForm(++currentRow);
        }

        private void btnPrev_Click(object sender, EventArgs e)
        {
            if (currentRow > 0) FillForm(--currentRow);
        }

        private void btnFind_Click(object sender, EventArgs e)
        {   
            // hlednai podle primarniho klice
            DataRow dr = tbl.Rows.Find(int.Parse(txtKey.Text));
            
            if (dr == null)
            {
                MessageBox.Show("Nenalezeno");
            }
            else
            {
                MessageBox.Show(dr["Jmeno"].ToString());
            }
        
        }


    }
}

Kód: Vybrat vše

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;
// pridano 
using System.Data.SqlClient;
namespace DBRelations
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        SqlConnection conn;
        SqlCommand cmdSelect;
        SqlDataAdapter adapter;
        
        DataSet ds;

        DataRelation relCatProds;
        UniqueConstraint uc;


        int CurrentRow;

        private void Form1_Load(object sender, EventArgs e)
        {
            conn = new SqlConnection();
            conn.ConnectionString = "User ID=sa;Password=Pa$$w0rd;Data Source=MIREK-THINKPAD\\SQLEXPRESS;Initial Catalog=Northwind";

            cmdSelect = new SqlCommand();
            cmdSelect.Connection = conn;
            cmdSelect.CommandText = "Select CategoryID, CategoryName From Categories";

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = cmdSelect;

            ds = new DataSet();

            adapter.Fill(ds, "Categories");

            // naplneni 2. tabulky
            // v praxi je lepsi mit pro kazdou tabulku vlastni SQL commandy
            // nejlepsi ale je mit pro kazdou tabulku vlastni DataAdapter
            cmdSelect.CommandText = "Select CategoryID,ProductName " +
            "From Products";
            adapter.Fill(ds, "Products");
            // vytvoreni relace
            relCatProds = new DataRelation("CatProds",
            ds.Tables["Categories"].Columns["CategoryID"],
            ds.Tables["Products"].Columns["CategoryID"]);
            ds.Relations.Add(relCatProds); // pridani do kolekce relaci-NUTNE!!
            // vytvoreni constraint
            uc = new UniqueConstraint("UniqueName",
            ds.Tables["Categories"].Columns["CategoryName"]);
            // pridani do kolekce constraints - NUTNE!!
            ds.Tables["Categories"].Constraints.Add(uc);
            // true/false - zapnout/vypnout vsechny constraints v ds
            ds.EnforceConstraints = true; 

            FillForm(CurrentRow = 0);
        }

        void FillForm (int index)
        {
            DataRow dr = ds.Tables["Categories"].Rows[index];
            txtCategoryID.Text = dr["CategoryID"].ToString();
            txtCategoryName.Text = dr["CategoryName"].ToString();
            txtProducts.Clear();
            // .GetChildRows - pouziti relace k vyhledani odpovidajicich radku
            foreach (DataRow radek in dr.GetChildRows(relCatProds))
            {
                txtProducts.Text += radek["CategoryID"] + "; " +
                radek["ProductName"] + "\r\n";
            }
        }

        private void btnNext_Click(object sender, EventArgs e)
        {
            if (CurrentRow < ds.Tables["Categories"].Rows.Count - 1) FillForm(++CurrentRow);
        } 

        private void btnPrev_Click(object sender, EventArgs e)
        {
            if (CurrentRow > 0) FillForm(--CurrentRow);
        }

        private void btnUpdateName_Click(object sender, EventArgs e)
        {
            ds.Tables["Categories"].Rows[CurrentRow]["CategoryName"] =
            txtCategoryName.Text;
            // pri duplicite vznikne exception diky UniqueConstraint
        }

        private void btnDisplayConstraints_Click(object sender, EventArgs e)
        {
            txtProducts.Text = "Categories Table Constraints:\r\n";
            // zobrazi 2 UniqueConstraints: nase pridane a druhe,
            // vytvorene relaci
            foreach (Constraint c in ds.Tables["Categories"].Constraints)
            {
                txtProducts.Text += String.Format(" Constraint {0}; Typ: " +
                "{1}\r\n", c, c.GetType());
            }
            // zobrazi ForeignKeyConstraint (vznikle diky relaci) s vlastnosti
            // .UpdateRule nastavenou na Cascade
            txtProducts.Text += "\r\nProducts Table Constraints:\r\n";
            foreach (Constraint c in ds.Tables["Products"].Constraints)
            {
                txtProducts.Text += String.Format(" Constraint {0}; Typ: " +
                "{1}", c, c.GetType());
                if (c is ForeignKeyConstraint) txtProducts.Text +=
                String.Format("; Update Rule: {0}",
                ((ForeignKeyConstraint)c).UpdateRule);
                txtProducts.Text += "\r\n";
            }
        }
        private void btnUpdateID_Click(object sender, EventArgs e)
        {
            // pri update primarniho klice (v tabulce Categories DataSetu)
            // se automaticky zmeni (diky relaci s .UpdateRule = Cascade)
            // foreign keys v tabulce Products!!
            ds.Tables["Categories"].Rows[CurrentRow]["CategoryID"] =
            int.Parse(txtCategoryID.Text);
            FillForm(CurrentRow);
        }
    }
}

Kód: Vybrat vše

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
// pridano
using System.Data.SqlClient;
namespace DBPerPartes
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        SqlConnection conn;
        SqlCommand cmdSelect;
        SqlDataAdapter adapter;
        DataSet ds;
        DataView view;
        int CurrentRow;
        const int BUFFER_SIZE = 10; // zvoleny pocet radku bufferu (ukazka)
        int MinID, MaxID = 0; // vim, ze MINIMALNI ProductID je 1 nebo vetsi
        private void Form1_Load(object sender, EventArgs e)
        {
            conn = new SqlConnection();
            conn.ConnectionString = "User ID=sa;Password=Pa$$w0rd; " +
            "Data Source=MIREK-THINKPAD\\SQLEXPRESS;Initial Catalog=Northwind";
            cmdSelect = new SqlCommand();
            cmdSelect.Connection = conn;
            cmdSelect.CommandText = "Select ProductID,ProductName " +
            "From Products Order By ProductID";
            adapter = new SqlDataAdapter();
            adapter.SelectCommand = cmdSelect;
            ds = new DataSet();
            // nasledujici nacte 5000 radku od 100000 - neefektivni;
            // chyby pri insertech a deletech jinymi uzivateli
            // adapter.Fill(ds, 100000, 5000, "Products");
            // vytvori strukturu tabulky (prazdnou tabulku)
            adapter.FillSchema(ds, SchemaType.Source, "Products");
            view = new DataView(ds.Tables["Products"]);
            // chci mit data vzdy setridena vzestupne podle ProductID
            view.Sort = "ProductID";
            FillBufferUp();
            FillForm(CurrentRow = 0);
        }
        // nacteni jednoho bufferu "smerem nahoru" - uprava SQL dotazu
        void FillBufferUp()
        {
            // vyber max. BUFFER_SIZE recordu s hodnotou > MaxID
            string strSql = "Select Top " + BUFFER_SIZE +
            " ProductID, ProductName " +
           "From Products " +
           "Where ProductID > " + MaxID +
            " Order By ProductID";
            // lze definovat (podstatne slozitejsi) klauzuli where, i pokud
            // mame trideni podle jinych kriterii (napr. LastName, FirstName).
            // Casto je treba pridat do trideni Primary Key
            // (jako posledni polozku do Order By)
            txtSql.Text = strSql; // debug only
            cmdSelect.CommandText = strSql;
            ds.Tables["Products"].Rows.Clear(); // buffer se nevyprazdňuje!!
            adapter.Fill(ds, "Products");
            // nove minimalni a maximalni hodnoty ProductID (hodnoty setrideny)
            int MaxIndex = view.Count - 1;
            MinID = (int)view[0]["ProductID"];
            MaxID = (int)view[MaxIndex]["ProductID"];
        }
        // nalezeni max. hodnoty PoductID v DB
        int GetLastID()
        {
            int LastID;
            string strMax;
            SqlCommand cmdMax;
            strMax = "Select Max(ProductID) From Products";
            cmdMax = new SqlCommand();
            cmdMax.CommandText = strMax;
            cmdMax.Connection = conn;
            // connected pristup vyzaduje otevrene connection!!
            conn.Open();
            LastID = (int)cmdMax.ExecuteScalar(); // jedna hodnota
            conn.Close();
            return LastID;
        }
        // nacteni jednoho bufferu "smerem dolu" - uprava SQL dotazu
        void FillBufferDown()
        {
            // vyber max. BUFFER_SIZE recordu s hodnotou < MinID
            string strSql = "Select Top " + BUFFER_SIZE +
            " ProductID, ProductName " +
           "From Products " +
           "Where ProductID < " + MinID +
           " Order By ProductID DESC";
            txtSql.Text = strSql; // debug only
            cmdSelect.CommandText = strSql;
            ds.Tables["Products"].Rows.Clear();
            adapter.Fill(ds, "Products");
            int MaxIndex = view.Count - 1;
            MinID = (int)view[0]["ProductID"]; // OK - sort ve view zaridi
            MaxID = (int)view[MaxIndex]["ProductID"];
        }
        // nalezeni min. hodnoty PoductID v DB
        int GetFirstID()
        {
            int FirstID;
            string strMin;
            SqlCommand cmdMin;
            strMin = "Select Min(ProductID) From Products";
            cmdMin = new SqlCommand();
            cmdMin.CommandText = strMin;
            cmdMin.Connection = conn;
            conn.Open();
            FirstID = (int)cmdMin.ExecuteScalar();
            conn.Close();
            return FirstID;
        }
        void FillForm(int Index)
        {
            DataRowView dr = view[Index];
            txtProductID.Text = dr["ProductID"].ToString();
            txtProductName.Text = dr["ProductName"].ToString();
        }
        private void btFirst_Click(object sender, EventArgs e)
        {
            MaxID = 0;
            FillBufferUp(); // naplneni 1. bufferu
            FillForm(CurrentRow = 0);
        }
        private void btnPrev_Click(object sender, EventArgs e)
        {
            if (CurrentRow > 0)
                FillForm(--CurrentRow);
            else
            {
                // GetFirstID a FillBufferDown vykonavat pod lockingem
                // (v transakci) - aby nikdo nemohl odmazat radky v dobe
                // mezi GetFirstID a FillBufferDown
                // jsou pred FirstID jeste nejake radky?
                if (MinID > GetFirstID())
                {
                    FillBufferDown(); // nacteni dalsiho bufferu
                    FillForm(CurrentRow = view.Count - 1);
                }
            }
        }
        private void btnNext_Click(object sender, EventArgs e)
        {
            if (CurrentRow < view.Count - 1)
                FillForm(++CurrentRow);
            else
            {
                // GetLastID a FillBufferUp - opet pod lockigem
                // jsou za MaxID jeste nejake radky?
                if (MaxID < GetLastID())
                {
                    FillBufferUp(); // nacteni predchoziho bufferu
                    FillForm(CurrentRow = 0);
                }
            }
        }
        private void btnLast_Click(object sender, EventArgs e)
        {
            MinID = int.MaxValue;
            FillBufferDown(); // nacteni posledniho bufferu
            FillForm(CurrentRow = view.Count - 1);
        }
    }
}

DBVisual - made by wizard, no code required

Kód: Vybrat vše

use Northwind

Select Char(9) + Char(9) + Char(9) + 'new Category(' + CONVERT(nvarchar(2),[CategoryID]) + ',"' + [CategoryName] + '")' From Categories
Select Char(9) + Char(9) + Char(9) + 'new Zam(' + CONVERT(nvarchar(2), [EmployeeID]) + ',"' + [LastName] + '","' + [FirstName] +'","' + [TitleOfCourtesy] + '","' + City + '"),' From Employees


Select Char(9) + Char(9) + Char(9) + 'new Product(' + CONVERT(nvarchar(2),[ProductID]) + ',"' + [ProductName] + '",' + Convert(nvarchar(4),CategoryID) + ',' + Convert(nvarchar(4),[UnitsInStock]) + '),' From Products

Kód: Vybrat vše

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.Xml.Linq;

namespace LINQZaklad
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        class Zam
        {
            public int ID { get; set; } // automaticky generovane promenne (kvuli vazbe na DataGridView)
            public string Prijmeni { get; set; }
            public string Krestni { get; set; }
            public string Osloveni { get; set; }
            public string Mesto { get; set; }

            public Zam(int ID, string Prijmeni, string Krestni, string Osloveni, string Mesto)
            {
                this.ID = ID;
                this.Krestni = Krestni;
                this.Prijmeni = Prijmeni;
                this.Osloveni = Osloveni;
                this.Mesto = Mesto;
            }
        }
        class Category
        {
            public int CategoryID { get; set; }
            public string CategoryName { get; set; }
            public Category(int CategoryID, string CategoryName)
            {
                this.CategoryID = CategoryID;
                this.CategoryName = CategoryName;
            }
        }
        class Product
        {
            public int ProductID { get; set; }
            public string ProductName { get; set; }
            public int CategoryID { get; set; }
            public int UnitsInStock { get;set;}
            public Product(int ProductID, string ProductName, int CategoryID, int UnitsInStock)
            {
                this.ProductID = ProductID;
                this.ProductName = ProductName;
                this.CategoryID = CategoryID;
                this.UnitsInStock = UnitsInStock;
            }
        }

        List<Zam> zamestnanci = new List<Zam> {
   			new Zam(1,"Davolio","Nancyaaaaa","Ms.","Seattle"),
			new Zam(2,"Fuller","Andrew","Dr.","Tacoma"),
			new Zam(3,"Leverlingova","Janet","Ms.","Kirkland"),
			new Zam(4,"Peacock","Margaret","Mrs.","Redmond"),
			new Zam(5,"Buchanan","Steven","Mr.","London"),
			new Zam(6,"Suyama","Michael","Mr.","London"),
			new Zam(7,"King","Robert","Mr.","London"),
			new Zam(8,"Callahan","Laura","Ms.","Seattle"),
			new Zam(9,"Dodsworth","Anne","Ms.","London"),
       };
        List<Category> categories = new List<Category>
        {
    		new Category(1,"Beverages"),
			new Category(2,"Condiments"),
			new Category(3,"Confections"),
			new Category(4,"Dairy Products"),
			new Category(5,"Grains/Cereals"),
			new Category(6,"Meat/Poultry"),
			new Category(7,"Produce"),
			new Category(8,"Seafood")
        };
        List<Product> products = new List<Product>
            {
            new Product(1,"Chai",1,39),
			new Product(2,"Chang",1,17),
			new Product(3,"Aniseed Syrup",2,13),
			new Product(4,"Chef Anton's Cajun Seasoning",2,53),
			new Product(5,"Chef Anton's Gumbo Mix",2,0),
			new Product(6,"Grandma's Boysenberry Spread",2,120),
			new Product(7,"Uncle Bob's Organic Dried Pears",7,15),
			new Product(8,"Northwoods Cranberry Sauce",2,6),
			new Product(9,"Mishi Kobe Niku",6,29),
			new Product(10,"Ikura",8,31),
			new Product(11,"Queso Cabrales",4,22),
			new Product(12,"Queso Manchego La Pastora",4,86),
			new Product(13,"Konbu",8,24),
			new Product(14,"Tofu",7,35),
			new Product(15,"Genen Shouyu",2,39),
			new Product(16,"Pavlova",3,29),
			new Product(17,"Alice Mutton",6,0),
			new Product(18,"Carnarvon Tigers",8,42),
			new Product(19,"Teatime Chocolate Biscuits",3,25),
			new Product(20,"Sir Rodney's Marmalade",3,40),
			new Product(21,"Sir Rodney's Scones",3,3),
			new Product(22,"Gustaf's Knäckebröd",5,104),
			new Product(23,"Tunnbröd",5,61),
			new Product(24,"Guaraná Fantástica",1,20),
			new Product(25,"NuNuCa Nuß-Nougat-Creme",3,76),
			new Product(26,"Gumbär Gummibärchen",3,15),
			new Product(27,"Schoggi Schokolade",3,49),
			new Product(28,"Rössle Sauerkraut",7,26),
			new Product(29,"Thüringer Rostbratwurst",6,0),
			new Product(30,"Nord-Ost Matjeshering",8,10),
			new Product(31,"Gorgonzola Telino",4,0),
			new Product(32,"Mascarpone Fabioli",4,9),
			new Product(33,"Geitost",4,112),
			new Product(34,"Sasquatch Ale",1,111),
			new Product(35,"Steeleye Stout",1,20),
			new Product(36,"Inlagd Sill",8,112),
			new Product(37,"Gravad lax",8,11),
			new Product(38,"Côte de Blaye",1,17),
			new Product(39,"Chartreuse verte",1,69),
			new Product(40,"Boston Crab Meat",8,123),
			new Product(41,"Jack's New England Clam Chowder",8,85),
			new Product(42,"Singaporean Hokkien Fried Mee",5,26),
			new Product(43,"Ipoh Coffee",1,17),
			new Product(44,"Gula Malacca",2,27),
			new Product(45,"Rogede sild",8,5),
			new Product(46,"Spegesild",8,95),
			new Product(47,"Zaanse koeken",3,36),
			new Product(48,"Chocolade",3,15),
			new Product(49,"Maxilaku",3,10),
			new Product(50,"Valkoinen suklaa",3,65),
			new Product(51,"Manjimup Dried Apples",7,20),
			new Product(52,"Filo Mix",5,38),
			new Product(53,"Perth Pasties",6,0),
			new Product(54,"Tourtiere",6,21),
			new Product(55,"Pâté chinois",6,115),
			new Product(56,"Gnocchi di nonna Alice",5,21),
			new Product(57,"Ravioli Angelo",5,36),
			new Product(58,"Escargots de Bourgogne",8,62),
			new Product(59,"Raclette Courdavault",4,79),
			new Product(60,"Camembert Pierrot",4,19),
			new Product(61,"Sirop d'érable",2,113),
			new Product(62,"Tarte au sucre",3,17),
			new Product(63,"Vegie-spread",2,24),
			new Product(64,"Wimmers gute Semmelknödel",5,22),
			new Product(65,"Louisiana Fiery Hot Pepper Sauce",2,76),
			new Product(66,"Louisiana Hot Spiced Okra",2,4),
			new Product(67,"Laughing Lumberjack Lager",1,52),
			new Product(68,"Scottish Longbreads",3,6),
			new Product(69,"Gudbrandsdalsost",4,26),
			new Product(70,"Outback Lager",1,15),
			new Product(71,"Flotemysost",4,26),
			new Product(72,"Mozzarella di Giovanni",4,14),
			new Product(73,"Röd Kaviar",8,101),
			new Product(74,"Longlife Tofu",7,4),
			new Product(75,"Rhönbräu Klosterbier",1,125),
			new Product(76,"Lakkalikööri",1,57),
			new Product(77,"Original Frankfurter grüne Soße",2,32)
            };

        private void btnProved_Click(object sender, EventArgs e)
        {
            // LINQ to objects
            //var query = from z in zamestnanci select z; // vyber celeho objektu
            //var query = from z in zamestnanci select new { z.ID, z.Prijmeni };
            //var query = from z in zamestnanci select new { z.ID, z.Prijmeni };
            //var query = from z in zamestnanci select new { CisloZam = z.ID, Jmeno = z.Prijmeni };

            //grdData.DataSource = zamestnanci; // pro uplnost - take chodi
            //var query = from z in zamestnanci orderby z.Prijmeni descending select new { CisloZam = z.ID, Jmeno = z.Prijmeni };

            //var query = from z in zamestnanci where z.Osloveni == "Ms." select new { z.ID, z.Prijmeni };
            // where z.Osloveni == "Ms." && z.Mesto == "London"

            //var query = (from z in zamestnanci select new { z.ID, z.Prijmeni }).Skip(3).Take(4); // podpora strankovani 
            //var query = from p in products join c in categories on p.CategoryID equals c.CategoryID select new { p.ProductName, c.CategoryName, p.UnitsInStock};



            // LINQ to XML
            //var query = from z in XElement.Load("..\\..\\Employees.xml").Elements("employee") select z; // zobrazi ale cely podstrom
            var query = from z in XElement.Load(@"..\..\Employees.xml").Elements("employee")
             // mohu i anonymni objekt new { ... }
                    select new Zam((int)z.Element("id"),
                     (string)z.Element("last"),
                     (string)z.Element("first"),
                     (string)z.Element("title"),
                     (string)z.Element("city"));
            
            // "join" XML dokumentu
            /*var query = from c in XElement.Load(@"..\..\Categories.xml").Elements("category") 
                        join p in XElement.Load(@"..\..\Products.xml").Elements("product") 
                        on (int)c.Element("id") equals (int)p.Element("catid") 
            select new { 
                category = (string)c.Element("name"), 
                product = (string)p.Element("name"),
                units = (string)p.Element("units")
            };*/
            
            //grdData.DataSource = query.ToList<Zam>(); // take ok
            //grdData.DataSource = zamestnanci;
            grdData.DataSource = query.ToList();
            grdData.AutoResizeColumns();

            /*foreach (var x in query)
            {
                txtData.Text += x.ID + "; " + x.Prijmeni + "\r\n";
            }*/
            
        }

    
    }
}

Kód: Vybrat vše

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;

namespace LINQtoSQL
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        // zakladni objekt NWDataContext vytvoren visualnim navrharem
        // Solution Explorer -> Project -> Add -> New item -> Data -> LINQ to SQL
        // set name to NW (pojmenovat)
        // double click na NW.dbml a ze Server Explorer z prislusne DB natahat tabulky, Store Procedury... (nechtene sloupce lze vymazat)
        // nechtene sloupce vymazat (right mouse + delete)
        NWDataContext dc = new NWDataContext();

        private void btnDotaz_Click(object sender, EventArgs e)
        {
            // doazy v zasade stejne jako u LINQ to objects
            // (neni explicitne vytvareno pripojeni k DB, nejsou psany SQL dotazy ...)

            //var query = from z in dc.Employees                         select z;
            //var query = from z in dc.Employees where z.TitleOfCourtesy == "Ms." select new { z.EmployeeID, z.LastName, z.TitleOfCourtesy };

            //var query = from p in dc.Products group p by p.CategoryID into cat select new { Kategorie = cat.Key, Pocet = cat.Count()}; // agregace

            var query = from c in dc.Categories
                        join p in dc.Products
                        on c.CategoryID equals p.CategoryID
                        select new { c.CategoryName, p.ProductName, p.UnitsInStock };

            txtSql.Text = query.ToString(); // vrati sql
            dataGridView1.DataSource = query;

            
        }

        private void btnUpravy_Click(object sender, EventArgs e)
        {
            var zam = dc.Employees.Single(z => z.EmployeeID == 1);
            zam.LastName = zam.LastName + "AA";

            dc.SubmitChanges();
        }

        private void btnVlozit_Click(object sender, EventArgs e)
        {
            Employee novy = new Employee
            {
                LastName = "Novak",
                FirstName = "Franta",
                TitleOfCourtesy = "Mr.",
                City = "Prague"
            };
            dc.Employees.InsertOnSubmit(novy); // objekt ma byt vlozen
            dc.SubmitChanges();
        }

        private void btnVymazat_Click(object sender, EventArgs e)
        {
            var query = from z in dc.Employees
                        where (z.LastName == "Novak") select z;

            // pro vymaz jednoho pripadu - opet pomoci Single a DeleteOnSubmit
            dc.Employees.DeleteAllOnSubmit(query);
            dc.SubmitChanges();
        }
    }
}

Kód: Vybrat vše

use Northwind

Select	CategoryID as 'id',
		CategoryName as 'name'
From Categories For XML Path('category'), root('categories')

Select EmployeeID as 'id', FirstName as 'first', LastName as 'last', City as 'city'
From Employees For XML Path('employee'), root('employees')

Select ProductID as 'id', ProductName as 'name', CategoryID as 'catid', UnitsInStock as 'units'
From Products For XML Path('product'), root('products')

Kód: Vybrat vše

// Entity Framework je novejsi technologie firmy MS pro tvorbu aplikaci,
// ktere pracuji nad databazemi. (U MS se zjevne „inspirovali“ projektem
// NHibernate.) EF je vybudovan nad infrastrukturou ADO.NET, vykladaneho
// v predchozi casti predmetu BI-PCS.
/*
 Program v EF je mozno navrhnou tremi zpusoby. Bud se (vizualnimi nastroji EF)
 navrhne objektovy model aplikace (entity model) a z neho se generuje jednak
 vlastni databaze a jednak vazby mezi modelem a databazi – Model First.

 Druha moznost je vyjit z existujici database, z ni (vizualnimi nastroji)
 vytvorit objektovy model a vazby – Database First
 Noveji lze definovat standardni tridy v programu + tzv. context (objekt,
 ktery zajistuje vazbu s databazi) a prislusne kolekce v tomto objektu.
 To vse se provede bez pouziti vizualnich nastroju – pristup Code First.
 Opet jsou dve podvarianty – s existujici databazi, nebo EF vygeneruje
 kod, ktery novou DB vytvori.
 Entity model (soubor Northwind.edmx) byl vytvoren z databaze
 vizualnimi nastroji Visual Studia (napr. spustenim wizzardu)
 .edmx obsahuje XML popisy:
 - entitniho modelu (cast CSDL - conceptual schema definition language)
 - databaze ci jineho uloziste (cast SSDL - store schema definition language)
 - mapovani mezi nimi (cast MSL - mapping specification language)
*/
using System;
using System.Drawing;
using System.Linq;
using System.Windows.Forms;
// pridano kvuli DbUpdateConcurrencyException
using System.Data.Entity.Infrastructure;
namespace EF_NW
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        // u starsiho Visual Studio je treba pridat (pomoci NuGet) vlastní EF
        // spusteni wizzardu - pridat „ADO.NET Entity Data Model“ do projektu
        // velmi se doporuje zatrhout checkbox „Pluralize or singularize …“
        private NorthwindEntities NWContext; // NorthwindEntities – vytvoril
        // visualni designer (Object Context)
        // Jako nazev (pole Name ve wizzardu) jsme zadali Northwind

        private void btnClose_Click(object sender, EventArgs e)
        {
            NWContext.Dispose();
            this.Close();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            // sloupce GridView (vcetne zdroje) vytvoreny předem ve visualnim
            // navrhari, aby se nezobrazovaly Navigation Properties (nechceme),
            // ani TimeStamp (pada) a dalsi nepozadovane vlastnosti
            grdProducts.AutoGenerateColumns = false;
            // nastaveni dalsich vlastnosti DataGridView
            grdProducts.AllowUserToDeleteRows = false;
            grdProducts.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
            BindEntities();
        }
        void BindEntities()
        {
            NWContext = new NorthwindEntities();
            // mozno nastavit (true - implicitni hodnota) - napr.:
            // NWContext.Configuration.LazyLoadingEnabled = false;
            // pro debug - pise do console (popr. file) vytvarene SQL prikazy
            NWContext.Database.Log = Console.Write;
            // Definice query, ktera vraci vsechny objekty Category a Product,
            // setridene dle jmena
            var categoryQuery = from d in NWContext.Categories.Include("Products")
                                orderby d.CategoryName
                                select d;
            try
            {
                // Navazani ComboBoxu na query, vykonavanou v prubehu
                // data bindingu.
                this.cboCategories.DisplayMember = "CategoryName";
                this.cboCategories.DataSource = categoryQuery.ToList();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void cboCategories_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                // Nacteni Productu pro zvolenou Category
                Categories _category = (Categories)cboCategories.SelectedItem;
                // Navazani (binding) grid view na kolekci objektu Product,
                // ktere odpovidaji zvolene Category (pro dane CategoryID)
                grdProducts.DataSource = _category.Products.ToList();
                // soucasne nastaveni textoveho pole pro insert
                txtCategoryIDNew.Text = _category.CategoryID.ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        // Pro detekovani DbUpdateConcurrency Exception je bud pridan TimeStamp
        // (RowVersion)a pro nej nastavena property ConcurrencyMode (ve
        // visualnim designeru edmx). Nebo je mozne se obejit i bez TS, ale
        // ConcurrencyMode se nastavi u tech property, kde chceme detekovat
        // kolize - concurrency (casto u vsech, ktere nejsou ReadOnly)
        private void btnSave_Click(object sender, EventArgs e)
        {
            btnSetCurrentDataAndSave.Enabled = false;
            btnSetCurrentDataAndSave.ForeColor = SystemColors.ControlText;
            bool saveFailed;
            do
            {
                saveFailed = false;
                try
                {

                    // Ulozi zmeny objektu do databaze, zobrazi hlasku,
                    // a provede refresh formu.
                    NWContext.SaveChanges();
                    MessageBox.Show("Zmeny ulozeny do databaze.");
                    this.Refresh();
                }
                catch (DbUpdateConcurrencyException ex)
                { // using!!
                    btnSetCurrentDataAndSave.Enabled = true;
                    btnSetCurrentDataAndSave.ForeColor = Color.Red;
                    // Reseni optimistic concurrency exception (kolize):
                    // 1. varianta – database wins (obnovit data v entity z DB)
                    // pomoci metody Reload (data entity prepsana databazovymi)
                    // saveFailed = true;
                    // ex.Entries.Single().Reload();
                    // MessageBox.Show("Kolize - v objektu obnovena data " +
                    // "z DB\r\n" + ex.Message);
                    // this.Refresh(); // prekresleni formu
                    // 2. varianta – client wins (prepsat data v DB z entity)
                    // saveFailed = true;
                    // var entry = ex.Entries.Single();
                    // entry.OriginalValues.SetValues(entry.GetDatabaseValues());
                    // MessageBox.Show("Kolize - do DB budou zapsana data " +
                    // "z objektu\r\n" + ex.Message);
                    // 3. varianta – uzivatel zvoli, jaka data (ci jejich
                    // kombinaci nebo upravene hodnoty) chce po kolizi ulozit.
                    // K tomu se mu zobrazi jak jeho aktualni data (oeditovana),
                    // tak originalni (ze kterych pri editaci vychazel) a
                    // nactou se i aktualni data z DB. (Nekdy muze byt reseno
                    // i automatizovane logikou, realizovanou kodem.)
                    saveFailed = false; // ulozi (pripadne) az uživatel
                    var entry = ex.Entries.Single(); // informace o datech
                    var currentValues = entry.CurrentValues; // aktualni
                    var originalValues = entry.OriginalValues; // originalni
                    var databaseValues = entry.GetDatabaseValues(); // DB
                    // zobrazeni uzivateli
                    DisplayConcurrencyValues(currentValues,
                    originalValues, databaseValues);
                    // existuje record v DB? (nebyl vymazan nebo zmenen PK
                    // (zde neni mozne - identity))
                    if (databaseValues != null)
                        // nastavit original na DB, aby znovu nehlasilo kolizi:
                        entry.OriginalValues.SetValues(databaseValues);
                    else
                        // znovu nacist grid po vymazani polozky
                        grdProducts.DataSource = ((Categories)this.cboCategories.SelectedItem).Products.ToList();

                    BindEntities(); // aby se vse znovu nacetlo
                    // totez pomoci metod ToObject a SetValues objektu
                    // DbPropertyValues
                    /*
                    saveFailed = false;
                     var entry = ex.Entries.Single();
                    var currentValues = entry.CurrentValues;
                    var originalValues = entry.OriginalValues;
                    var databaseValues = entry.GetDatabaseValues();
                     var databaseValuesAsProduct =
                     (Product)databaseValues.ToObject();
                     var originalValuesAsProduct =
                     (Product)entry.OriginalValues.ToObject();
                     var currentValuesAsProduct =
                     (Product)entry.CurrentValues.ToObject();
                     DisplayConcurrencyValuesAsProducts(currentValuesAsProduct,
                     originalValuesAsProduct, databaseValuesAsProduct);
                     if (databaseValues != null)
                     // aby znovu nehlasilo kolizi
                     entry.OriginalValues.SetValues(databaseValues);
                     else
                     // znovu nacist grid po vymazani polozky
                     grdProducts.DataSource =
                     ((Category)this.cboCategories.SelectedItem).Products.ToList();

                     BindEntities(); // aby se vse znovu nacetlo
                     */
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            } while (saveFailed);
        }
        // funkce pro zobrazeni dat uzivateli
        public void DisplayConcurrencyValues(DbPropertyValues currentValues,
        DbPropertyValues originalValues,
        DbPropertyValues databaseValues)
        {
            // aktualni hodnoty
            txtProductID.Text = currentValues["ProductID"].ToString();
            txtProductName.Text = currentValues["ProductName"].ToString();
            txtCategoryID.Text = currentValues["CategoryID"].ToString();
            //txtUnitPrice.Text = currentValues["UnitPrice"].ToString();
            txtUnitsInStock.Text = currentValues["UnitsInStock"].ToString();
            // originalni hodnoty
            txtProductIDOrig.Text = originalValues["ProductID"].ToString();
            txtProductNameOrig.Text = originalValues["ProductName"].ToString();
            txtCategoryIDOrig.Text = originalValues["CategoryID"].ToString();
            //txtUnitPriceOrig.Text = originalValues["UnitPrice"].ToString();
            txtUnitsInStockOrig.Text = originalValues["UnitsInStock"].ToString();
            // soucasne hodnoty z databaze
            if (databaseValues == null)
            { // nenalezeno - deletovano? zmenen PK?
                // (zde neni mozne menit PK)
                missing.Visible = true;
                txtProductIDDB.Text = txtProductNameDB.Text = txtCategoryIDDB.Text = txtUnitsInStockDB.Text = ""; // txtUnitPriceDB.Text
            }
            else
            {
                missing.Visible = false;
                txtProductIDDB.Text = databaseValues["ProductID"].ToString();
                txtProductNameDB.Text = databaseValues["ProductName"].ToString();
                txtCategoryIDDB.Text = databaseValues["CategoryID"].ToString();
                //txtUnitPriceDB.Text = databaseValues["UnitPrice"].ToString();
                txtUnitsInStockDB.Text = databaseValues["UnitsInStock"].ToString();
            }
        }
        // totez pomoci objektu Product:
        /*
 public void DisplayConcurrencyValuesAsProducts(Product currentValues,
 Product originalValues,
Product databaseValues) {
 txtProductID.Text = currentValues.ProductID.ToString();
 txtProductName.Text = currentValues.ProductName.ToString();
 txtCategoryID.Text = currentValues.CategoryID.ToString();
 txtUnitPrice.Text = currentValues.UnitPrice.ToString();
 txtUnitsInStock.Text = currentValues.UnitsInStock.ToString();
 txtProductIDOrig.Text = originalValues.ProductID.ToString();
 txtProductNameOrig.Text = originalValues.ProductName.ToString();
 txtCategoryIDOrig.Text = originalValues.CategoryID.ToString();
 txtUnitPriceOrig.Text = originalValues.UnitPrice.ToString();
 txtUnitsInStockOrig.Text = originalValues.UnitsInStock.ToString();
 if (databaseValues == null) { // nenalezeno - deletovano? zmenen PK?
 // (zde nelze)
 missing.Visible = true;
 txtProductIDDB.Text = txtProductNameDB.Text =
 txtCategoryIDDB.Text =
 txtUnitPriceDB.Text = txtUnitsInStockDB.Text = "";
 }
 else {
 missing.Visible = false;
 txtProductIDDB.Text = databaseValues.ProductID.ToString();
 txtProductNameDB.Text = databaseValues.ProductName.ToString();
 txtCategoryIDDB.Text = databaseValues.CategoryID.ToString();
 txtUnitPriceDB.Text = databaseValues.UnitPrice.ToString();
 txtUnitsInStockDB.Text = databaseValues.UnitsInStock.ToString();
 }
 }
 */
        private void btnSetCurrentDataAndSave_Click(object sender, EventArgs e)
        {
            // uzivatel uklada data po kolizi
            btnSetCurrentDataAndSave.Enabled = false;
            btnSetCurrentDataAndSave.ForeColor = SystemColors.ControlText;
            if (missing.Visible)
            { // record s danym ProductID neni v DB
                // nakopirovani dat pro insert
                txtProductNameNew.Text = txtProductName.Text;
                //txtUnitPriceNew.Text = txtUnitPrice.Text;
                txtUnitsInStockNew.Text = txtUnitsInStock.Text;
                txtCategoryIDNew.Text = txtCategoryID.Text;
                btnInsert_Click(null, null);
            }
            else
            {
                // vlozeni zmen do objektu
                var product = NWContext.Products.Find(int.Parse(txtProductID.Text));
                product.CategoryID = int.Parse(txtCategoryID.Text);
                product.ProductName = txtProductName.Text;
                //product.UnitPrice = decimal.Parse(txtUnitPrice.Text);
                product.UnitsInStock = short.Parse(txtUnitsInStock.Text);
            }
            btnSave_Click(null, null);
        }
        private void btnInsert_Click(object sender, EventArgs e)
        {
            // vlozeni noveho objektu
            btnSetCurrentDataAndSave.Enabled = false;
            btnSetCurrentDataAndSave.ForeColor = SystemColors.ControlText;
            // pridani objektu (Product)
            Products newProduct = new Products();
            // neprideluji - udela autoincrement v DB:
            // newProduct.ProductID = 123;
            newProduct.ProductName = txtProductNameNew.Text;
            //newProduct.UnitPrice = decimal.Parse(txtUnitPriceNew.Text);
            newProduct.UnitsInStock = short.Parse(txtUnitsInStockNew.Text);
            newProduct.Discontinued = false; // nepovoluje null v DB
            newProduct.CategoryID = int.Parse(txtCategoryIDNew.Text);
            NWContext.Products.Add(newProduct);
            // refresh gridu (znovunavazani DataSource)
            grdProducts.DataSource = ((Categories)this.cboCategories.SelectedItem).Products.ToList();
        }
        private void btnDelete_Click(object sender, EventArgs e)
        {
            // vymaz objektu
            btnSetCurrentDataAndSave.Enabled = false;
            btnSetCurrentDataAndSave.ForeColor = SystemColors.ControlText;
            if (grdProducts.SelectedRows.Count != 1)
            {
                MessageBox.Show("Pro vymaz musite zvolit jeden radek");
                return;
            }
            int ProductID = (int)grdProducts.SelectedRows[0].Cells["ProductID"].Value;
            NWContext.Products.Remove(NWContext.Products.Find(ProductID));
            // refresh gridu (znovunavazani DataSource)
            grdProducts.DataSource = ((Categories)this.cboCategories.SelectedItem).Products.ToList();
        }
        private void btnQuery_Click(object sender, EventArgs e)
        {
            // ukazka LINQ dotazu pouzitych v EF
            btnSetCurrentDataAndSave.Enabled = false;
            btnSetCurrentDataAndSave.ForeColor = SystemColors.ControlText;
            // query 1
            var query = NWContext.Products;
            // query 2
            // var query = from p in NWContext.Products
            //select new {
            //	p.ProductID, p.ProductName
            //};
            // query 2a – totez jako 2 pomoci lambda vyrazu a metody Select
            // var query = NWContext.Products.Select(c = > new {c.ProductID, c.ProductName});
            // query 3 – trideni (pomoci metody OrderBy)
            // var query = NWContext.Products.OrderBy(x => x.ProductName);
            // query 4 - produkty, zacinajici pismenem M
            // var query = from p in NWContext.Products
            // where p.ProductName.StartsWith("M")
            // select p;
            // query 5 .FirstOrDefault - pro vyber jednoho objektu
            // (neni kolekce, nema .ToList)
            // Product p = NWContext.Products // mozne i var misto Product.Where(b = > b.ProductName == "Chai").FirstOrDefault();
            // MessageBox.Show(p.ProductID + " " + p.ProductName + " " +
            // p.UnitsInStock);
            // query 6 – stejny dotaz jako 5, ale vraci kolekci objektu
            // (ta ma metodu .ToList, i kdyz kolekce obsahuje jediny objekt)
            // var query = NWContext.Products.Where(b = > b.ProductName == "Chai");
            // query 7 - vyuziti navigacni vlastnosti - join neni nutno psat
            // explicite (p.Category.CategoryName - prejit do druhe kolekce)
            // Category – pointer na objekt Category pro dany Product
            // var query = from p in NWContext.Products
            // select new { p.ProductID, p.ProductName,p.Category.CategoryName};

            // bezny GridView, ktery automaticky vytvari potrebne sloupce
            grdOutput.DataSource = query.ToList(); // zakomentovat pro query 5
            // zneviditelnit pripadnou navigacni property
            if (grdOutput.Columns.Contains("Category")) grdOutput.Columns["Category"].Visible = false;
        }
    }
}

Kód: Vybrat vše

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;

namespace NW_CodeFirst_FromDB
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        Northwind NWContext = new Northwind();

        // classes Northwind (dedic DbContext), Categories, Products vytvoreny wizardem spustenym pridanim "ADO.NET Entity Data Model"; jmeno Northwind jsem zadal jako prvni name ve wizardu
        // zatrzenim checkbox "Pluralize or Singularize ..." (kolekce na konci ...s)
        // AppConfig - take jsem pouzil nazev connection stringu Northwind

        private void Form1_Load(object sender, EventArgs e)
        {
            // sloupce DataGridView nastaveny ve vizualnim navrhari (vcetne jmen ze zdroje)
            grdProducts.AutoGenerateColumns = false;
            grdProducts.AllowUserToDeleteRows = false;
            grdProducts.AutoResizeColumns(DataGridViewAutoSizeColumnMode.AllCells);
            // pro debug
            NWContext.Database.Log = Console.Write; // vypis SQL prikazu
        }

    }
}

Kód: Vybrat vše

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;

namespace EF_CodeFirst_NoDB
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        //NWContext NW = new NWContext();

        private void Form1_Load(object sender, EventArgs e)
        {
            // sloupce DataGridView nastaveny ve vizualnim navrhari (vcetne jmen ze zdroje)
            grdProducts.AutoGenerateColumns = false;
            grdProducts.AllowUserToDeleteRows = false;
            /*
            // pro debug
            NW.Database.Log = Console.Write; // vypis SQL prikazu


            var categoryQuery = from d in NW.Categories.Include("Products")
                                orderby d.CategoryName
                                select d;
            try
            {
                // Navazani ComboBoxu na query, vykonavanou v prubehu
                // data bindingu.
                this.cboCategories.DisplayMember = "CategoryName";
                this.cboCategories.DataSource = categoryQuery.ToList();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
             * */
        }

        private void cboCategories_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                // navazani datagrid na products, odpovidajici aktualni hodnote combo boxu
                //grdProducts.DataSource = ((Category)cboCategories.SelectedItem).Products.ToList();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnSaveToDB_Click(object sender, EventArgs e)
        {
            try
            {
                // ulozeni zmen do DB
                //NW.SaveChanges();
                MessageBox.Show("Zmeny ulozeny do DB");
                this.Refresh();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnFillDB1st_Click(object sender, EventArgs e)
        {
            // jenom ukazka vytvoreni a naplneni db
            using (var NW = new NWContext())
            {
                NW.Database.Log = Console.Write;

                Category _category = new Category() {CategoryName = "Beverages"};
                NW.Categories.Add(_category);

                _category = new Category() {CategoryName = "Diary Products"};
                NW.Categories.Add(_category);

                _category = new Category() {CategoryName = "DansGame"};
                NW.Categories.Add(_category);
            }
        }
    }
}

[Destroyer]

https://www.fit-wiki.cz/_media/%C5%A1ko ... jpg?cache= spravne
https://www.fit-wiki.cz/_media/%C5%A1ko ... jpg?cache= 6 si nejsem jistej
https://www.fit-wiki.cz/_media/%C5%A1ko ... jpg?cache= 2 nejsem si jistej
4 je podle me d
https://www.fit-wiki.cz/_media/%C5%A1ko ... jpg?cache=
6b
8b, pokud se timestampem mysli rowversion tak nic z predchoziho je taky teoreticky mozny i kdyz u b) je obvykle takze...
10b
https://www.fit-wiki.cz/_media/%C5%A1ko ... jpg?cache= 2c
u 4 si nejsem jistej jestli je chyba zarucena nebo ne, ale urcite je vysoka sance
https://www.fit-wiki.cz/_media/%C5%A1ko ... jpg?cache=
3c
6?
9c?
10c
12a
13d?
14d?