MySqlConnection – oflazoglu.org

Etiket: MySqlConnection

NYP-2 2016 8. Hafta Uygulamaları

caglarPosted on 9 Mayıs 2016 C#, Ders Notları, Nesneye Yönelik Programlama-II, Yazılım kategorisine gönderildi No Comments

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 MySql.Data.MySqlClient;

namespace puanlama
{
    public partial class Form1 : Form
    {
        MySqlDataAdapter da_katsayilar, da_kisiler, da_puanlar;
        DataTable dt_katsayilar, dt_kisiler, dt_puanlar;
        public Form1()
        {
            InitializeComponent();
            MySqlConnectionStringBuilder bag = new MySqlConnectionStringBuilder();
            bag.Server = "localhost";
            bag.UserID = "root";
            bag.Password = "123456";
            bag.Database = "hafta8_16_2";

            MySqlConnection conn = new MySqlConnection(bag.ToString());

            MySqlCommand cmd_katsayilar_sel = 
                new MySqlCommand("select id,adi,puan from katsayilar;", conn);

            da_katsayilar = new MySqlDataAdapter();
            da_katsayilar.SelectCommand = cmd_katsayilar_sel;

            MySqlCommand cmd_kisiler_sel = 
                new MySqlCommand("select id,isimler from kisiler order by isimler;", conn);
            da_kisiler = new MySqlDataAdapter();
            da_kisiler.SelectCommand = cmd_kisiler_sel;

            MySqlCommand cmd_puanlar_sel = 
                new MySqlCommand("select id,kisiid,puanturu, miktar from puanlar;",conn);
            
            MySqlCommand cmd_puanlar_ins =
                new MySqlCommand("insert into puanlar(kisiid,puanturu,miktar) values(@kisiid,@puanturu,@miktar);", conn);
            cmd_puanlar_ins.Parameters.Add("@kisiid", MySqlDbType.Int32, 11, "kisiid");
            cmd_puanlar_ins.Parameters.Add("@puanturu", MySqlDbType.Int32, 11, "puanturu");
            cmd_puanlar_ins.Parameters.Add("@miktar", MySqlDbType.Int32, 11, "miktar");

            MySqlCommand cmd_puanlar_del =
               new MySqlCommand("delete from puanlar where id=@id;", conn);
            cmd_puanlar_del.Parameters.Add("@id",MySqlDbType.Int32,11,"id");
          
            da_puanlar = new MySqlDataAdapter();
            da_puanlar.SelectCommand = cmd_puanlar_sel;
            da_puanlar.DeleteCommand = cmd_puanlar_del;
            da_puanlar.InsertCommand = cmd_puanlar_ins;
            listedoldur();
        }

        private void bpuanlama_Click(object sender, EventArgs e)
        {
            fpuanlama frm = new fpuanlama(da_katsayilar, da_kisiler, da_puanlar);
            frm.ShowDialog();
            listedoldur();
        }

        private void kisilerigetir()
        {
            try
            {
                dt_kisiler = new DataTable();
                da_kisiler.Fill(dt_kisiler);
            }
            catch
            { }
        }
        

        private void katsayilarigetir()
        {
            try
            {
                dt_katsayilar = new DataTable();
                da_katsayilar.Fill(dt_katsayilar);
            }
            catch
            { }
        }

        private void puanlarigetir()
        {
            try
            {
                dt_puanlar = new DataTable();
                da_puanlar.Fill(dt_puanlar);
            }
            catch
            { }
        }

        private void listedoldur()
        {
            kisilerigetir();
            katsayilarigetir();
            puanlarigetir();

            lisim.Items.Clear();
            lpuan.Items.Clear();
            lderece.Items.Clear();
            int say = dt_kisiler.Rows.Count;

            for (int i = 0; i < say; i++)
            {
                int kisi_id = (int)dt_kisiler.Rows[i]["id"];
                int puantoplami = puantoplamihesapla(kisi_id);
                if (puantoplami > 0)
                {
                    lisim.Items.Add(dt_kisiler.Rows[i]["isimler"]);
                    lpuan.Items.Add(puantoplami);
                    lderece.Items.Add(derecehesapla(puantoplami));
                }
            }        
        }

        private int puantoplamihesapla(int kisiid)
        {
            int toplam = 0;
            // puanlistesi
            DataRow[] dr = dt_puanlar.Select("kisiid=" + kisiid);

            for (int i = 0; i < dr.Length; i++)
            {
                int miktar = (int)dr[i]["miktar"];
                int puanturu_id = (int)dr[i]["puanturu"];
                // puan katsayilari
                int katsayi = katsayibul(puanturu_id);
                toplam += miktar * katsayi;
            }
            return toplam;
        }

        private int katsayibul(int katsayi_id)
        {
            DataRow[] dr = dt_katsayilar.Select("id=" + katsayi_id);
            if (dr.Length > 0)
                return (int)dr[0]["puan"];
            return 0;
        }

        private string derecehesapla(int toplampuan)
        { 
            // hesaplama alg. yazilacak
            // her 50 puan icin bir yildiz
            return "*";
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

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 MySql.Data.MySqlClient;

namespace puanlama

{

public partial class Form1 : Form

{

MySqlDataAdapter da_katsayilar, da_kisiler, da_puanlar;

DataTable dt_katsayilar, dt_kisiler, dt_puanlar;

public Form1()

{

InitializeComponent();

MySqlConnectionStringBuilder bag = new MySqlConnectionStringBuilder();

bag.Server = "localhost";

bag.UserID = "root";

bag.Password = "123456";

bag.Database = "hafta8_16_2";

MySqlConnection conn = new MySqlConnection(bag.ToString());

MySqlCommand cmd_katsayilar_sel =

new MySqlCommand("select id,adi,puan from katsayilar;", conn);

da_katsayilar = new MySqlDataAdapter();

da_katsayilar.SelectCommand = cmd_katsayilar_sel;

MySqlCommand cmd_kisiler_sel =

new MySqlCommand("select id,isimler from kisiler order by isimler;", conn);

da_kisiler = new MySqlDataAdapter();

da_kisiler.SelectCommand = cmd_kisiler_sel;

MySqlCommand cmd_puanlar_sel =

new MySqlCommand("select id,kisiid,puanturu, miktar from puanlar;",conn);

MySqlCommand cmd_puanlar_ins =

new MySqlCommand("insert into puanlar(kisiid,puanturu,miktar) values(@kisiid,@puanturu,@miktar);", conn);

cmd_puanlar_ins.Parameters.Add("@kisiid", MySqlDbType.Int32, 11, "kisiid");

cmd_puanlar_ins.Parameters.Add("@puanturu", MySqlDbType.Int32, 11, "puanturu");

cmd_puanlar_ins.Parameters.Add("@miktar", MySqlDbType.Int32, 11, "miktar");

MySqlCommand cmd_puanlar_del =

new MySqlCommand("delete from puanlar where id=@id;", conn);

cmd_puanlar_del.Parameters.Add("@id",MySqlDbType.Int32,11,"id");

da_puanlar = new MySqlDataAdapter();

da_puanlar.SelectCommand = cmd_puanlar_sel;

da_puanlar.DeleteCommand = cmd_puanlar_del;

da_puanlar.InsertCommand = cmd_puanlar_ins;

listedoldur();

}

private void bpuanlama_Click(object sender, EventArgs e)

{

fpuanlama frm = new fpuanlama(da_katsayilar, da_kisiler, da_puanlar);

frm.ShowDialog();

listedoldur();

}

private void kisilerigetir()

{

try

{

dt_kisiler = new DataTable();

da_kisiler.Fill(dt_kisiler);

}

catch

{ }

}

private void katsayilarigetir()

{

try

{

dt_katsayilar = new DataTable();

da_katsayilar.Fill(dt_katsayilar);

}

catch

{ }

}

private void puanlarigetir()

{

try

{

dt_puanlar = new DataTable();

da_puanlar.Fill(dt_puanlar);

}

catch

{ }

}

private void listedoldur()

{

kisilerigetir();

katsayilarigetir();

puanlarigetir();

lisim.Items.Clear();

lpuan.Items.Clear();

lderece.Items.Clear();

int say = dt_kisiler.Rows.Count;

for (int i = 0; i < say; i++)

{

int kisi_id = (int)dt_kisiler.Rows[i]["id"];

int puantoplami = puantoplamihesapla(kisi_id);

if (puantoplami > 0)

{

lisim.Items.Add(dt_kisiler.Rows[i]["isimler"]);

lpuan.Items.Add(puantoplami);

lderece.Items.Add(derecehesapla(puantoplami));

}

private int puantoplamihesapla(int kisiid)

{

int toplam = 0;

// puanlistesi

DataRow[] dr = dt_puanlar.Select("kisiid=" + kisiid);

for (int i = 0; i < dr.Length; i++)

{

int miktar = (int)dr[i]["miktar"];

int puanturu_id = (int)dr[i]["puanturu"];

// puan katsayilari

int katsayi = katsayibul(puanturu_id);

toplam += miktar * katsayi;

}

return toplam;

}

private int katsayibul(int katsayi_id)

{

DataRow[] dr = dt_katsayilar.Select("id=" + katsayi_id);

if (dr.Length > 0)

return (int)dr[0]["puan"];

return 0;

}

private string derecehesapla(int toplampuan)

{

// hesaplama alg. yazilacak

// her 50 puan icin bir yildiz

return "*";

}

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 MySql.Data.MySqlClient;

namespace puanlama
{
    public partial class fpuanlama : Form
    {
        MySqlDataAdapter da_katsayilar, da_kisiler, da_puanlar;
        DataTable dt_katsayilar, dt_kisiler, dt_puanlar;
        public fpuanlama(MySqlDataAdapter _da1,MySqlDataAdapter _da2,MySqlDataAdapter _da3)
        {
            InitializeComponent();

            da_katsayilar = _da1;
            da_kisiler = _da2;
            da_puanlar = _da3;
            listedoldur();

        }

        private void isimdoldur()
        {
            cisim.Items.Clear();
            
            try
            {
                dt_kisiler = new DataTable();
                da_kisiler.Fill(dt_kisiler);

                int say = dt_kisiler.Rows.Count;
                for (int i = 0; i < say; i++)
                {
                    cisim.Items.Add(dt_kisiler.Rows[i]["isimler"]);
                }
            }
            catch
            { 
            }
            cisim.Text = "Seçiniz";
        }

        private void puanturudoldur()
        {
            cpuanturu.Items.Clear();

            try
            {
                dt_katsayilar  = new DataTable();
                da_katsayilar.Fill(dt_katsayilar);

                int say = dt_katsayilar.Rows.Count;
                for (int i = 0; i < say; i++)
                {
                    cpuanturu.Items.Add(dt_katsayilar.Rows[i]["adi"]);
                }
            }
            catch
            {
            }
            cpuanturu.Text = "Seçiniz";
        }


        private void listedoldur()
        {
            isimdoldur();
            puanturudoldur();

            lisim.Items.Clear();
            lpuanturu.Items.Clear();
            ladet.Items.Clear();

            try
            {
                dt_puanlar = new DataTable();
                da_puanlar.Fill(dt_puanlar);

                int say = dt_puanlar.Rows.Count;
                for (int i = 0; i < say; i++)
                {
                    DataRow dr_kisi = 
                        tablodangetir(dt_kisiler, (int)dt_puanlar.Rows[i]["kisiid"]);
                    DataRow dr_tur =
                        tablodangetir(dt_katsayilar, (int)dt_puanlar.Rows[i]["puanturu"]);

                    if (dr_kisi != null && dr_tur != null)
                    {
                        lisim.Items.Add(dr_kisi["isimler"]);
                        lpuanturu.Items.Add(dr_tur["adi"]);
                        ladet.Items.Add(dt_puanlar.Rows[i]["miktar"]);
                    }
                   /* else
                    {
                     // isim ve tur tanimi veritabaninda yer almiyor ise
                        lisim.Items.Add("-");
                        lpuanturu.Items.Add("-");
                        ladet.Items.Add(dt_puanlar.Rows[i]["miktar"]);
                    }*/
                }
            }
            catch
            { 
            
            }
        }

        private DataRow tablodangetir(DataTable dt,int id)
        {
            DataRow[] dr = dt.Select("id=" + id);
            if (dr.Length > 0)
                return dr[0];
            return null;
        }

        private void bekle_Click(object sender, EventArgs e)
        {
            int s_isim = cisim.SelectedIndex;
            int s_puanturu = cpuanturu.SelectedIndex;
            int miktar;
            bool k = Int32.TryParse(tadet.Text, out miktar);

            if ( k && s_isim > -1 && s_puanturu > -1)
            {
                try
                {
                    DataRow dr = dt_puanlar.NewRow();
                    dr["kisiid"] = (int)dt_kisiler.Rows[s_isim]["id"];
                    dr["puanturu"] = (int)dt_katsayilar.Rows[s_puanturu]["id"];
                    dr["miktar"] = miktar;
                    dt_puanlar.Rows.Add(dr);
                    da_puanlar.Update(dt_puanlar);
                }
                catch
                { 
                }

                tadet.Clear();
                listedoldur();
            }



        }

        private void bsil_Click(object sender, EventArgs e)
        {
            int secilen = lisim.SelectedIndex;

            if (secilen > -1)
            {
                try
                {
                    // dt_puanlar.Select(""); // tanimsiz bilgileri silmek icin tercih edilebilir
                    dt_puanlar.Rows[secilen].Delete();
                    da_puanlar.Update(dt_puanlar);
                }
                catch
                { }
                listedoldur();
            }


        }

        private void bkapat_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

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 MySql.Data.MySqlClient;

namespace puanlama

{

public partial class fpuanlama : Form

{

MySqlDataAdapter da_katsayilar, da_kisiler, da_puanlar;

DataTable dt_katsayilar, dt_kisiler, dt_puanlar;

public fpuanlama(MySqlDataAdapter _da1,MySqlDataAdapter _da2,MySqlDataAdapter _da3)

{

InitializeComponent();

da_katsayilar = _da1;

da_kisiler = _da2;

da_puanlar = _da3;

listedoldur();

}

private void isimdoldur()

{

cisim.Items.Clear();

try

{

dt_kisiler = new DataTable();

da_kisiler.Fill(dt_kisiler);

int say = dt_kisiler.Rows.Count;

for (int i = 0; i < say; i++)

{

cisim.Items.Add(dt_kisiler.Rows[i]["isimler"]);

}

catch

{

}

cisim.Text = "Seçiniz";

}

private void puanturudoldur()

{

cpuanturu.Items.Clear();

try

{

dt_katsayilar = new DataTable();

da_katsayilar.Fill(dt_katsayilar);

int say = dt_katsayilar.Rows.Count;

for (int i = 0; i < say; i++)

{

cpuanturu.Items.Add(dt_katsayilar.Rows[i]["adi"]);

}

catch

{

}

cpuanturu.Text = "Seçiniz";

}

private void listedoldur()

{

isimdoldur();

puanturudoldur();

lisim.Items.Clear();

lpuanturu.Items.Clear();

ladet.Items.Clear();

try

{

dt_puanlar = new DataTable();

da_puanlar.Fill(dt_puanlar);

int say = dt_puanlar.Rows.Count;

for (int i = 0; i < say; i++)

{

DataRow dr_kisi =

tablodangetir(dt_kisiler, (int)dt_puanlar.Rows[i]["kisiid"]);

DataRow dr_tur =

tablodangetir(dt_katsayilar, (int)dt_puanlar.Rows[i]["puanturu"]);

if (dr_kisi != null && dr_tur != null)

{

lisim.Items.Add(dr_kisi["isimler"]);

lpuanturu.Items.Add(dr_tur["adi"]);

ladet.Items.Add(dt_puanlar.Rows[i]["miktar"]);

}

/* else

{

// isim ve tur tanimi veritabaninda yer almiyor ise

lisim.Items.Add("-");

lpuanturu.Items.Add("-");

ladet.Items.Add(dt_puanlar.Rows[i]["miktar"]);

}*/

}

catch

{

}

private DataRow tablodangetir(DataTable dt,int id)

{

DataRow[] dr = dt.Select("id=" + id);

if (dr.Length > 0)

return dr[0];

return null;

}

private void bekle_Click(object sender, EventArgs e)

{

int s_isim = cisim.SelectedIndex;

int s_puanturu = cpuanturu.SelectedIndex;

int miktar;

bool k = Int32.TryParse(tadet.Text, out miktar);

if ( k && s_isim > -1 && s_puanturu > -1)

{

try

{

DataRow dr = dt_puanlar.NewRow();

dr["kisiid"] = (int)dt_kisiler.Rows[s_isim]["id"];

dr["puanturu"] = (int)dt_katsayilar.Rows[s_puanturu]["id"];

dr["miktar"] = miktar;

dt_puanlar.Rows.Add(dr);

da_puanlar.Update(dt_puanlar);

}

catch

{

}

tadet.Clear();

listedoldur();

}

private void bsil_Click(object sender, EventArgs e)

{

int secilen = lisim.SelectedIndex;

if (secilen > -1)

{

try

{

// dt_puanlar.Select(""); // tanimsiz bilgileri silmek icin tercih edilebilir

dt_puanlar.Rows[secilen].Delete();

da_puanlar.Update(dt_puanlar);

}

catch

{ }

listedoldur();

}

private void bkapat_Click(object sender, EventArgs e)

{

this.Close();

}

NYP-2 2016 Hafta-8

Etiketler: mysql, MySqlCommand, MySqlConnection, MySqlConnectionStringBuilder, MysqlDataAdapter

NYP-2 2016 6. Hafta Uygulamaları

caglarPosted on 17 Mart 2016 C#, Ders Notları, Nesneye Yönelik Programlama-II, Yazılım kategorisine gönderildi No Comments

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 MySql.Data.MySqlClient;


namespace alisverissepeti
{
    public partial class Form1 : Form
    {
        DataTable dt1, dt2;
        MySqlDataAdapter da1, da2;

        public Form1()
        {
            InitializeComponent();
            rbalis.Checked = true;

            MySqlConnectionStringBuilder bag = new MySqlConnectionStringBuilder();
            bag.Server = "localhost";
            bag.UserID = "root";
            bag.Password = "123456";
            bag.Database = "hafta6_16_2";

            MySqlConnection conn = new MySqlConnection(bag.ToString());

            // urun tablosu sorgulari
            MySqlCommand cmd_urun_sel = 
                new MySqlCommand("select id, urunadi, alisfiyati, satisfiyati from urun", conn);
            da1 = new MySqlDataAdapter();
            da1.SelectCommand = cmd_urun_sel;

            // sepet tablosu sorgulari
            MySqlCommand cmd_sepet_sel = 
                new MySqlCommand("select id,urunid,miktar,tip from sepet", conn);
            MySqlCommand cmd_sepet_ins = 
                new MySqlCommand("insert into sepet(urunid,miktar,tip) values(@urunid,@miktar,@tip)", conn);
            cmd_sepet_ins.Parameters.Add("@urunid", MySqlDbType.Int32, 11, "urunid");
            cmd_sepet_ins.Parameters.Add("@miktar", MySqlDbType.Int32, 11, "miktar");
            cmd_sepet_ins.Parameters.Add("@tip", MySqlDbType.Int32, 11, "tip");
            MySqlCommand cmd_sepet_del = 
                new MySqlCommand("delete from sepet where id=@id", conn);
            cmd_sepet_del.Parameters.Add("@id", MySqlDbType.Int32, 11, "id");

            da2 = new MySqlDataAdapter();
            da2.SelectCommand = cmd_sepet_sel;
            da2.InsertCommand = cmd_sepet_ins;
            da2.DeleteCommand = cmd_sepet_del;
            urungetir();
            sepetgetir();
        }

        private void urungetir()
        {
            curun.Items.Clear();
            try
            {
                dt1 = new DataTable();
                da1.Fill(dt1);
                int satirsay = dt1.Rows.Count;
                for (int i = 0; i < satirsay; i++)
                    curun.Items.Add(dt1.Rows[i]["urunadi"]);

                curun.Text = "Seçiniz";
            }
            catch
            {
                curun.Text = "Hata";
            }
        }

        private void sepetgetir()
        { 
            double tutar=0;
            lurun.Items.Clear();
            lmiktar.Items.Clear();
            lislem.Items.Clear();
            ltutar.Items.Clear();
            try
            {
                dt2 = new DataTable();
                da2.Fill(dt2);

                int satirsay = dt2.Rows.Count;
                for (int i = 0; i < satirsay; i++)
                {
                    int urunid= (int)dt2.Rows[i]["urunid"];
                    int miktar = (int)dt2.Rows[i]["miktar"];
                    int tip = (int)dt2.Rows[i]["tip"];
                    double uruntutar = 0;
                    DataRow dr = urunbul(urunid);
                    if (dr != null)
                    {
                        lurun.Items.Add(dr["urunadi"]);
                        lmiktar.Items.Add(miktar);
                        if (tip == 0)
                        {
                            lislem.Items.Add("Alış");
                            uruntutar = miktar * (double)dr["alisfiyati"];
                            tutar -= uruntutar;
                        }
                        else
                        {
                            lislem.Items.Add("Satış");
                            uruntutar = miktar * (double)dr["satisfiyati"];
                            tutar += uruntutar;
                        }
                        ltutar.Items.Add(uruntutar);
                    }
                }

                curun.Text = "Seçiniz";
            }
            catch
            {
                curun.Text = "Hata";
            }
            latutar.Text = tutar + " TL";
        }

        private DataRow urunbul(int id)
        {
            int satirsay = dt1.Rows.Count;
            for(int i=0;i<satirsay;i++)
            {
                if ((int)dt1.Rows[i]["id"] == id)
                    return dt1.Rows[i];
            }
            return null;

            
        }

         private DataRow urunbul1(int id)
        {
            try
            {
                DataRow[] dr = dt1.Select("id=" + id); // datatable icerisinde yer alan bir kolon ile ilgili filtre
                if (dr.Length > 0)
                    return dr[0];
            }
            catch
            { 
            
            }

            return null;
        }

        private void bekle_Click(object sender, EventArgs e)
        {
            int miktar;
            int secilen = curun.SelectedIndex;
            bool kontrol = Int32.TryParse(tmiktar.Text, out miktar);

            if (secilen > -1 && kontrol)
            {
                try
                {
                    DataRow dr = dt2.NewRow();
                    dr["urunid"] = dt1.Rows[secilen]["id"];// ekleme siralari ayni oldugu icin 
                    dr["miktar"] = miktar;
                    if (rbalis.Checked)
                        dr["tip"] = 0;
                    else
                        dr["tip"] = 1;

                    dt2.Rows.Add(dr);
                    da2.Update(dt2);
                    curun.Text = "Seçiniz";
                    rbalis.Checked = true;
                    tmiktar.Clear();
                }
                catch
                { }
                sepetgetir(); // liste guncelle
            }
        }

        private void bsil_Click(object sender, EventArgs e)
        {
            int secilen = lurun.SelectedIndex;
            if (secilen > -1)
            {
                try
                {
                    dt2.Rows[secilen].Delete();
                    da2.Update(dt2);
                }
                catch
                { }
                sepetgetir(); // listeyi guncelle
            }
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

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 MySql.Data.MySqlClient;

namespace alisverissepeti

{

public partial class Form1 : Form

{

DataTable dt1, dt2;

MySqlDataAdapter da1, da2;

public Form1()

{

InitializeComponent();

rbalis.Checked = true;

MySqlConnectionStringBuilder bag = new MySqlConnectionStringBuilder();

bag.Server = "localhost";

bag.UserID = "root";

bag.Password = "123456";

bag.Database = "hafta6_16_2";

MySqlConnection conn = new MySqlConnection(bag.ToString());

// urun tablosu sorgulari

MySqlCommand cmd_urun_sel =

new MySqlCommand("select id, urunadi, alisfiyati, satisfiyati from urun", conn);

da1 = new MySqlDataAdapter();

da1.SelectCommand = cmd_urun_sel;

// sepet tablosu sorgulari

MySqlCommand cmd_sepet_sel =

new MySqlCommand("select id,urunid,miktar,tip from sepet", conn);

MySqlCommand cmd_sepet_ins =

new MySqlCommand("insert into sepet(urunid,miktar,tip) values(@urunid,@miktar,@tip)", conn);

cmd_sepet_ins.Parameters.Add("@urunid", MySqlDbType.Int32, 11, "urunid");

cmd_sepet_ins.Parameters.Add("@miktar", MySqlDbType.Int32, 11, "miktar");

cmd_sepet_ins.Parameters.Add("@tip", MySqlDbType.Int32, 11, "tip");

MySqlCommand cmd_sepet_del =

new MySqlCommand("delete from sepet where id=@id", conn);

cmd_sepet_del.Parameters.Add("@id", MySqlDbType.Int32, 11, "id");

da2 = new MySqlDataAdapter();

da2.SelectCommand = cmd_sepet_sel;

da2.InsertCommand = cmd_sepet_ins;

da2.DeleteCommand = cmd_sepet_del;

urungetir();

sepetgetir();

}

private void urungetir()

{

curun.Items.Clear();

try

{

dt1 = new DataTable();

da1.Fill(dt1);

int satirsay = dt1.Rows.Count;

for (int i = 0; i < satirsay; i++)

curun.Items.Add(dt1.Rows[i]["urunadi"]);

curun.Text = "Seçiniz";

}

catch

{

curun.Text = "Hata";

}

private void sepetgetir()

{

double tutar=0;

lurun.Items.Clear();

lmiktar.Items.Clear();

lislem.Items.Clear();

ltutar.Items.Clear();

try

{

dt2 = new DataTable();

da2.Fill(dt2);

int satirsay = dt2.Rows.Count;

for (int i = 0; i < satirsay; i++)

{

int urunid= (int)dt2.Rows[i]["urunid"];

int miktar = (int)dt2.Rows[i]["miktar"];

int tip = (int)dt2.Rows[i]["tip"];

double uruntutar = 0;

DataRow dr = urunbul(urunid);

if (dr != null)

{

lurun.Items.Add(dr["urunadi"]);

lmiktar.Items.Add(miktar);

if (tip == 0)

{

lislem.Items.Add("Alış");

uruntutar = miktar * (double)dr["alisfiyati"];

tutar -= uruntutar;

}

else

{

lislem.Items.Add("Satış");

uruntutar = miktar * (double)dr["satisfiyati"];

tutar += uruntutar;

}

ltutar.Items.Add(uruntutar);

}

curun.Text = "Seçiniz";

}

catch

{

curun.Text = "Hata";

}

latutar.Text = tutar + " TL";

}

private DataRow urunbul(int id)

{

int satirsay = dt1.Rows.Count;

for(int i=0;i<satirsay;i++)

{

if ((int)dt1.Rows[i]["id"] == id)

return dt1.Rows[i];

}

return null;

}

private DataRow urunbul1(int id)

{

try

{

DataRow[] dr = dt1.Select("id=" + id); // datatable icerisinde yer alan bir kolon ile ilgili filtre

if (dr.Length > 0)

return dr[0];

}

catch

{

}

return null;

}

private void bekle_Click(object sender, EventArgs e)

{

int miktar;

int secilen = curun.SelectedIndex;

bool kontrol = Int32.TryParse(tmiktar.Text, out miktar);

if (secilen > -1 && kontrol)

{

try

{

DataRow dr = dt2.NewRow();

dr["urunid"] = dt1.Rows[secilen]["id"];// ekleme siralari ayni oldugu icin

dr["miktar"] = miktar;

if (rbalis.Checked)

dr["tip"] = 0;

else

dr["tip"] = 1;

dt2.Rows.Add(dr);

da2.Update(dt2);

curun.Text = "Seçiniz";

rbalis.Checked = true;

tmiktar.Clear();

}

catch

{ }

sepetgetir(); // liste guncelle

}

private void bsil_Click(object sender, EventArgs e)

{

int secilen = lurun.SelectedIndex;

if (secilen > -1)

{

try

{

dt2.Rows[secilen].Delete();

da2.Update(dt2);

}

catch

{ }

sepetgetir(); // listeyi guncelle

}

CREATE SCHEMA `hafta6_16_2` DEFAULT CHARACTER SET utf8 COLLATE utf8_turkish_ci ;

CREATE TABLE `hafta6_16_2`.`urun` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `urunadi` VARCHAR(45) NULL COMMENT '',
  `alisfiyati` DOUBLE NULL COMMENT '',
  `satisfiyati` DOUBLE NULL COMMENT '',
  PRIMARY KEY (`id`)  COMMENT '');

  CREATE TABLE `hafta6_16_2`.`sepet` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `urunid` INT NULL COMMENT '',
  `miktar` INT NULL COMMENT '',
  `tip` INT NULL COMMENT '',
  PRIMARY KEY (`id`)  COMMENT '');

INSERT INTO `hafta6_16_2`.`urun` (`urunadi`, `alisfiyati`, `satisfiyati`) VALUES ('Masa', '25', '45');
INSERT INTO `hafta6_16_2`.`urun` (`urunadi`, `alisfiyati`, `satisfiyati`) VALUES ('Sandalye', '15', '20');
INSERT INTO `hafta6_16_2`.`urun` (`urunadi`, `alisfiyati`, `satisfiyati`) VALUES ('Sehpa', '5', '15');
INSERT INTO `hafta6_16_2`.`urun` (`urunadi`, `alisfiyati`, `satisfiyati`) VALUES ('TV', '1200', '1350');
INSERT INTO `hafta6_16_2`.`urun` (`urunadi`, `alisfiyati`, `satisfiyati`) VALUES ('Halı', '450', '600');

CREATE SCHEMA `hafta6_16_2` DEFAULT CHARACTER SET utf8 COLLATE utf8_turkish_ci ;

CREATE TABLE `hafta6_16_2`.`urun` (

`id` INT NOT NULL AUTO_INCREMENT COMMENT '',

`urunadi` VARCHAR(45) NULL COMMENT '',

`alisfiyati` DOUBLE NULL COMMENT '',

`satisfiyati` DOUBLE NULL COMMENT '',

PRIMARY KEY (`id`) COMMENT '');

CREATE TABLE `hafta6_16_2`.`sepet` (

`id` INT NOT NULL AUTO_INCREMENT COMMENT '',

`urunid` INT NULL COMMENT '',

`miktar` INT NULL COMMENT '',

`tip` INT NULL COMMENT '',

PRIMARY KEY (`id`) COMMENT '');

INSERT INTO `hafta6_16_2`.`urun` (`urunadi`, `alisfiyati`, `satisfiyati`) VALUES ('Masa', '25', '45');

INSERT INTO `hafta6_16_2`.`urun` (`urunadi`, `alisfiyati`, `satisfiyati`) VALUES ('Sandalye', '15', '20');

INSERT INTO `hafta6_16_2`.`urun` (`urunadi`, `alisfiyati`, `satisfiyati`) VALUES ('Sehpa', '5', '15');

INSERT INTO `hafta6_16_2`.`urun` (`urunadi`, `alisfiyati`, `satisfiyati`) VALUES ('TV', '1200', '1350');

INSERT INTO `hafta6_16_2`.`urun` (`urunadi`, `alisfiyati`, `satisfiyati`) VALUES ('Halı', '450', '600');

NYP-2 2016 6. Hafta Uygulamaları

Etiketler: c#, c# veritabanı, csharp, DataTable, delete, DeleteCommand, insert, InsertCommand, mysql, MySqlConnection, MySqlConnectionStringBuilder, MysqlDataAdapter, MySqlDbType, Parameters, Parameters.Add(), select, SelectCommand, update

NYP-2 2016 5. Hafta Uygulamaları

caglarPosted on 9 Mart 2016 C#, Ders Notları, Nesneye Yönelik Programlama-II, Yazılım kategorisine gönderildi No Comments

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 MySql.Data.MySqlClient;

namespace depo
{
    public partial class Form1 : Form
    {
        MySqlDataAdapter da;
        DataTable dt;

        public Form1()
        {
            InitializeComponent();

            MySqlConnectionStringBuilder bag = new MySqlConnectionStringBuilder();
            bag.Server = "localhost";
            bag.UserID = "root";
            bag.Password = "123456";
            bag.Database = "hafta5_16_1";

            MySqlConnection conn = new MySqlConnection(bag.ToString());
            MySqlCommand cmd_sel = new MySqlCommand("select id,isim,miktar from depo;", conn);
            MySqlCommand cmd_ins = new MySqlCommand("insert into depo(isim,miktar) values (@isim,@miktar);",conn);
            cmd_ins.Parameters.Add("@isim", MySqlDbType.VarChar, 45, "isim");
            cmd_ins.Parameters.Add("@miktar", MySqlDbType.Int32, 11, "miktar");

            MySqlCommand cmd_del = new MySqlCommand("delete from depo where id=@id", conn);
            cmd_del.Parameters.Add("@id", MySqlDbType.Int32, 11, "id");

            MySqlCommand cmd_upd = new MySqlCommand("update depo set isim=@isim, miktar=@miktar where id=@id",conn);
            cmd_upd.Parameters.Add("@id", MySqlDbType.Int32, 11, "id");
            cmd_upd.Parameters.Add("@isim", MySqlDbType.VarChar, 45, "isim");
            cmd_upd.Parameters.Add("@miktar", MySqlDbType.Int32, 11, "miktar");

            da = new MySqlDataAdapter();
            da.SelectCommand = cmd_sel;
            da.InsertCommand = cmd_ins;
            da.DeleteCommand = cmd_del;
            da.UpdateCommand = cmd_upd;

            verigetir();
        }

        private void bekle_Click(object sender, EventArgs e)
        {
            int miktar;
            bool kontrol = Int32.TryParse(tmiktar.Text, out miktar);

            if (kontrol && turun.Text.Length > 0)
            {

                try
                {
                    DataRow dr = dt.NewRow(); // mevcut tablodan yeni bir satir turettik
                    dr["isim"] = turun.Text;
                    dr["miktar"] = miktar;
                    dt.Rows.Add(dr); // olusturulan yeni satir tabloya eklendi
                    da.Update(dt);
                    turun.Clear();
                    tmiktar.Clear();

                }
                catch
                { 
                }

                verigetir();
            }



        }


        private void verigetir()
        {
            try
            {
                dt = new DataTable();
                da.Fill(dt);
                lurun.Items.Clear();
                lmiktar.Items.Clear();

                int satirsay = dt.Rows.Count;
                for (int i = 0; i < satirsay; i++)
                {
                    lurun.Items.Add(dt.Rows[i]["isim"]);
                    lmiktar.Items.Add(dt.Rows[i]["miktar"]);
                }
            }
            catch { 
            
            }
        
        
        }

        private void bsil_Click(object sender, EventArgs e)
        {

            int index = lurun.SelectedIndex;
            if (index > -1)
            {
                try
                {
                    dt.Rows[index].Delete();
                    da.Update(dt);
                }
                catch
                { 
                 
                }
                verigetir();
            }



        }

        private void bguncelleme_Click(object sender, EventArgs e)
        {
            int index = lurun.SelectedIndex;
             int miktar;
            bool kontrol = Int32.TryParse(tmiktar.Text, out miktar);

            if (kontrol && index>-1 && turun.Text.Length > 0)
            {

                try
                {
                    DataRow dr = dt.Rows[index]; // mevcut satiri getirdik
                    dr["isim"] = turun.Text;
                    dr["miktar"] = miktar;
                    da.Update(dt);
                    turun.Clear();
                    tmiktar.Clear();
                    verigetir();
                }
                catch
                { 
                    //
                }
            }


        }

    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

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 MySql.Data.MySqlClient;

namespace depo

{

public partial class Form1 : Form

{

MySqlDataAdapter da;

DataTable dt;

public Form1()

{

InitializeComponent();

MySqlConnectionStringBuilder bag = new MySqlConnectionStringBuilder();

bag.Server = "localhost";

bag.UserID = "root";

bag.Password = "123456";

bag.Database = "hafta5_16_1";

MySqlConnection conn = new MySqlConnection(bag.ToString());

MySqlCommand cmd_sel = new MySqlCommand("select id,isim,miktar from depo;", conn);

MySqlCommand cmd_ins = new MySqlCommand("insert into depo(isim,miktar) values (@isim,@miktar);",conn);

cmd_ins.Parameters.Add("@isim", MySqlDbType.VarChar, 45, "isim");

cmd_ins.Parameters.Add("@miktar", MySqlDbType.Int32, 11, "miktar");

MySqlCommand cmd_del = new MySqlCommand("delete from depo where id=@id", conn);

cmd_del.Parameters.Add("@id", MySqlDbType.Int32, 11, "id");

MySqlCommand cmd_upd = new MySqlCommand("update depo set isim=@isim, miktar=@miktar where id=@id",conn);

cmd_upd.Parameters.Add("@id", MySqlDbType.Int32, 11, "id");

cmd_upd.Parameters.Add("@isim", MySqlDbType.VarChar, 45, "isim");

cmd_upd.Parameters.Add("@miktar", MySqlDbType.Int32, 11, "miktar");

da = new MySqlDataAdapter();

da.SelectCommand = cmd_sel;

da.InsertCommand = cmd_ins;

da.DeleteCommand = cmd_del;

da.UpdateCommand = cmd_upd;

verigetir();

}

private void bekle_Click(object sender, EventArgs e)

{

int miktar;

bool kontrol = Int32.TryParse(tmiktar.Text, out miktar);

if (kontrol && turun.Text.Length > 0)

{

try

{

DataRow dr = dt.NewRow(); // mevcut tablodan yeni bir satir turettik

dr["isim"] = turun.Text;

dr["miktar"] = miktar;

dt.Rows.Add(dr); // olusturulan yeni satir tabloya eklendi

da.Update(dt);

turun.Clear();

tmiktar.Clear();

}

catch

{

}

verigetir();

}

private void verigetir()

{

try

{

dt = new DataTable();

da.Fill(dt);

lurun.Items.Clear();

lmiktar.Items.Clear();

int satirsay = dt.Rows.Count;

for (int i = 0; i < satirsay; i++)

{

lurun.Items.Add(dt.Rows[i]["isim"]);

lmiktar.Items.Add(dt.Rows[i]["miktar"]);

}

catch {

}

private void bsil_Click(object sender, EventArgs e)

{

int index = lurun.SelectedIndex;

if (index > -1)

{

try

{

dt.Rows[index].Delete();

da.Update(dt);

}

catch

{

}

verigetir();

}

private void bguncelleme_Click(object sender, EventArgs e)

{

int index = lurun.SelectedIndex;

int miktar;

bool kontrol = Int32.TryParse(tmiktar.Text, out miktar);

if (kontrol && index>-1 && turun.Text.Length > 0)

{

try

{

DataRow dr = dt.Rows[index]; // mevcut satiri getirdik

dr["isim"] = turun.Text;

dr["miktar"] = miktar;

da.Update(dt);

turun.Clear();

tmiktar.Clear();

verigetir();

}

catch

{

}

CREATE SCHEMA `hafta5_16_1` DEFAULT CHARACTER SET utf8 COLLATE utf8_turkish_ci ;
CREATE TABLE `hafta5_16_1`.`depo` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `isim` VARCHAR(45) NULL COMMENT '',
  `miktar` INT NULL COMMENT '',
  PRIMARY KEY (`id`)  COMMENT '');

CREATE SCHEMA `hafta5_16_1` DEFAULT CHARACTER SET utf8 COLLATE utf8_turkish_ci ;

CREATE TABLE `hafta5_16_1`.`depo` (

`id` INT NOT NULL AUTO_INCREMENT COMMENT '',

`isim` VARCHAR(45) NULL COMMENT '',

`miktar` INT NULL COMMENT '',

PRIMARY KEY (`id`) COMMENT '');

NYP-2 2016 5. Hafta Uygulamaları