WINFORM数据库操作,有点像安装里面的SQLITE

  1. 程序设计要求

    设计一个用户管理系统,对系统中的用户进行管理。假定,用户表中有下列字段:用户名,密码,电话和 email 等信息。要求,1)利用 SQL server 首先创建用户数据表;2)实现对用户的增加、删除、修改和查询等功能。

    2.程序设计流程图

    3.程序设计亮点

    (0)根据学生在使用CMS系统和Discuz系统时,发现其数据库的后台操作界面很简单,因此根据sql语句的基本组成,设置where条件选择输入区和更新插入的更新区。

    (1)只有在勾选该字段时,才允许输入该字段的值

    (2)使用listview列出数据查询结果

    (3)支持数据浏览,无须手动输入查询语句

    4.程序运行截图

    以下只是简单的几组测试,其他的sql组合同理能够运行

    (1) 浏览数据

    注:选择浏览操作的示意图

    (2)选择查看符合相应条件的数据

    注:设置选择的条件示意图

    注:执行选择后的结果

    (3)插入数据

    注:插入数据示意

    注:数据插入后,浏览数据结果

    (4)更新数据

    注:设置更新条件示意图

    注:执行更新操作后

    (5)删除数据

    注:设置删除条件示意图

    注:删除操作执行后

    1. using System;
    2. using System.Collections.Generic;
    3. using System.ComponentModel;
    4. using System.Data;
    5. using System.Drawing;
    6. using System.Linq;
    7. using System.Text;
    8. using System.Windows.Forms;
    9. using System.Data;
    10. using System.Data.Sql;
    11. using System.Data.SqlClient;
    12. namespace 数据库操作_WNFORM
    13. {
    14. public partial class Form1 : Form
    15. {
    16. private SqlConnection sqlcon;
    17. private SqlCommand sqlcom;
    18. private SqlDataReader sda;
    19. private string conn_str = @”Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|info.mdf;Integrated Security=True;User Instance=True”;
    20. public Form1()
    21. {
    22. InitializeComponent();
    23. }

    24. private void Form1_Load(object sender, EventArgs e)
    25. {
    26. listView_res.Columns.Add(“用户名”);
    27. listView_res.Columns.Add(“密 码”);
    28. listView_res.Columns.Add(“手机号”);
    29. listView_res.Columns.Add(“邮箱号”);
    30. listView_res.Columns[0].Width = 250;
    31. listView_res.Columns[1].Width = 340;
    32. listView_res.Columns[2].Width = 245;
    33. listView_res.Columns[3].Width = 325;
    34. }

    35. private void checkBox1_CheckedChanged(object sender, EventArgs e)
    36. {
    37. textBox_name.Enabled = true;
    38. }

    39. private void checkBox3_CheckedChanged(object sender, EventArgs e)
    40. {
    41. textBox_pass.Enabled = true;
    42. }

    43. private void checkBox2_CheckedChanged(object sender, EventArgs e)
    44. {
    45. textBox_name2.Enabled = true;
    46. }

    47. private void checkBox6_CheckedChanged(object sender, EventArgs e)
    48. {
    49. textBox_pass2.Enabled = true;
    50. }

    51. private void checkBox4_CheckedChanged(object sender, EventArgs e)
    52. {
    53. textBox_tel.Enabled = true;
    54. }

    55. private void checkBox5_CheckedChanged(object sender, EventArgs e)
    56. {
    57. textBox_mail.Enabled = true;
    58. }

    59. private void checkBox7_CheckedChanged(object sender, EventArgs e)
    60. {
    61. textBox_tel2.Enabled = true;
    62. }

    63. private void checkBox8_CheckedChanged(object sender, EventArgs e)
    64. {
    65. textBox_mail2.Enabled = true;
    66. }
    67. private void init_controler()
    68. {
    69. checkBox1.Checked = false;
    70. checkBox2.Checked = false;
    71. checkBox3.Checked = false;
    72. checkBox4.Checked = false;
    73. checkBox5.Checked = false;
    74. checkBox6.Checked = false;
    75. checkBox7.Checked = false;
    76. checkBox8.Checked = false;
    77. textBox_mail.Text = “”;
    78. textBox_name.Text = “”;
    79. textBox_pass.Text = “”;
    80. textBox_tel.Text = “”;
    81. textBox_mail2.Text = “”;
    82. textBox_name2.Text = “”;
    83. textBox_pass2.Text = “”;
    84. textBox_tel2.Text = “”;


    85. textBox_mail.Enabled = false;
    86. textBox_name.Enabled = false;
    87. textBox_pass.Enabled = false;
    88. textBox_tel.Enabled = false;
    89. textBox_mail2.Enabled = false;
    90. textBox_name2.Enabled = false;
    91. textBox_pass2.Enabled = false;
    92. textBox_tel2.Enabled = false;
    93. }
    94. private string [] get_where()
    95. {
    96. string [] res=new string[4];
    97. if (textBox_name.Text != null)
    98. {

    99. res[0]= textBox_name.Text.ToString();
    100. }
    101. if (textBox_pass.Text != null)
    102. {
    103. res[1]= textBox_pass.Text.ToString();
    104. }
    105. if (textBox_tel.Text != null)
    106. {
    107. res[2]= textBox_tel.Text.ToString();
    108. }

    109. if (textBox_mail.Text != null)
    110. {
    111. res[3]= textBox_mail.Text.ToString();
    112. }
    113. return res;
    114. }
    115. private string[] get_value()
    116. {
    117. string[] res = new string[4];
    118. if (textBox_name2.Text != null)
    119. {
    120. res[0] = textBox_name2.Text.ToString();
    121. }
    122. if (textBox_pass2.Text != null)
    123. {
    124. res[1] = textBox_pass2.Text.ToString();
    125. }
    126. if (textBox_tel2.Text != null)
    127. {
    128. res[2] = textBox_tel2.Text.ToString();
    129. }

    130. if (textBox_mail2.Text != null)
    131. {
    132. res[3] = textBox_mail2.Text.ToString();
    133. }
    134. return res;
    135. }
    136. //执行数据库查询
    137. private string execu_sql()
    138. {

    139. string res = “”;
    140. sqlcon = new SqlConnection(conn_str);
    141. sqlcom = new SqlCommand();
    142. sqlcon.Open();
    143. sqlcom.Connection = sqlcon;
    144. string sw = “”;
    145. sw = comboBox1.SelectedItem.ToString();
    146. MessageBox.Show(sw);
    147. /
    148. 浏览数据
    149. 查询数据
    150. 插入数据
    151. 更新数据
    152. 删除数据
    153. /
    154. switch (sw)
    155. {
    156. case “浏览数据”:
    157. res = “select * from [用户表]”;
    158. sqlcom.CommandText = res;
    159. int count = 0;
    160. try
    161. {
    162. sda = sqlcom.ExecuteReader();
    163. listView_res.Items.Clear();
    164. while (sda.Read())
    165. {
    166. ListViewItem lvi = new ListViewItem();
    167. lvi.Text = sda.GetString(0);
    168. lvi.SubItems.Add(sda.GetString(1));
    169. lvi.SubItems.Add(sda.GetString(2));
    170. lvi.SubItems.Add(sda.GetString(3));
    171. lvi.UseItemStyleForSubItems = false;
    172. lvi.SubItems[0].BackColor = Color.HotPink;
    173. lvi.SubItems[1].BackColor = Color.LightSkyBlue;
    174. lvi.SubItems[2].BackColor = Color.SpringGreen;
    175. lvi.SubItems[3].BackColor = Color.Orange;
    176. listView_res.Items.Add(lvi);
    177. count++;
    178. }
    179. if(count==0)
    180. {
    181. MessageBox.Show(“没有查询到匹配的数据”);
    182. }
    183. }
    184. catch (Exception ex)
    185. {
    186. MessageBox.Show(“出现数据查询错误,请重试” + ex.Message);
    187. }
    188. finally {
    189. sqlcon.Close();
    190. }
    191. break;
    192. case “查询数据”:
    193. res = “select * from [用户表]”;
    194. string [] wh=get_where();
    195. bool tags=false;
    196. if(wh!=null)
    197. {
    198. res+=” where “;
    199. if (wh[0]!= “”)
    200. {
    201. res += ” 用户名=@user “;
    202. //res+=wh[0];
    203. tags=true;
    204. }
    205. if (wh[1] != “”)
    206. {
    207. if(tags)
    208. res+=”and “;
    209. res+=” 密码=@pass “;
    210. //res+=wh[1];
    211. tags=true;
    212. }
    213. if (wh[2] != “”)
    214. {
    215. if(tags)
    216. res += ” and “;
    217. res+=” 电话=@tel “;
    218. //res+=wh[2];
    219. tags=true;
    220. }
    221. if(wh[3]!=”“)
    222. {
    223. if(tags)
    224. res += ” and “;
    225. res+=” 邮箱=@mail “;
    226. // res+=wh[1];
    227. }
    228. sqlcom.CommandText = res;
    229. if (wh != null)
    230. {
    231. if (wh[0] != “”)
    232. {
    233. sqlcom.Parameters.AddWithValue(“@user”, wh[0]);
    234. }
    235. if (wh[1] != “”)
    236. {
    237. sqlcom.Parameters.AddWithValue(“@pass”, wh[1]);
    238. }
    239. if (wh[2] != “”)
    240. {
    241. sqlcom.Parameters.AddWithValue(“@tel”, wh[2]);
    242. }
    243. if (wh[3] != “”)
    244. {
    245. sqlcom.Parameters.AddWithValue(“@mail”, wh[3]);
    246. }
    247. }
    248. try
    249. {
    250. sda = sqlcom.ExecuteReader();
    251. listView_res.Items.Clear();
    252. if (sda.Read())
    253. {
    254. ListViewItem lvi = new ListViewItem();
    255. lvi.Text = sda.GetString(0);
    256. lvi.SubItems.Add(sda.GetString(1));
    257. lvi.SubItems.Add(sda.GetString(2));
    258. lvi.SubItems.Add(sda.GetString(3));
    259. lvi.UseItemStyleForSubItems = false;
    260. lvi.SubItems[0].BackColor = Color.HotPink;
    261. lvi.SubItems[1].BackColor = Color.LightSkyBlue;
    262. lvi.SubItems[2].BackColor = Color.SpringGreen;
    263. lvi.SubItems[3].BackColor = Color.Orange;
    264. listView_res.Items.Add(lvi);
    265. }
    266. else
    267. {
    268. MessageBox.Show(“没有查询到匹配的数据,请检查原因”);
    269. }
    270. }
    271. catch (Exception ex)
    272. {
    273. MessageBox.Show(“出现数据查询错误,请重试” + ex.Message);
    274. }
    275. finally {
    276. sqlcon.Close();
    277. }
    278. }
    279. break;
    280. case “插入数据”:
    281. res = “insert into [用户表](“;
    282. string [] vl=get_value();
    283. bool tagi=false;
    284. if (vl != null)
    285. {
    286. if (vl[0] != “”)
    287. {
    288. res += “用户名”;
    289. tagi = true;
    290. }
    291. if (vl[1] != “”)
    292. {
    293. if (tagi)
    294. res+=” , “;
    295. res+=”密码 “;
    296. tagi = true;
    297. }
    298. if (vl[2] != “”)
    299. {
    300. if (tagi)
    301. res+=” , “;
    302. res+=”电话”;
    303. tagi = true;
    304. }
    305. if (vl[3] != “”)
    306. {
    307. if (tagi)
    308. res+=” , “;
    309. res+=”邮箱”;
    310. }
    311. }
    312. res += ” ) values(“;
    313. /*** 构造values *******/
    314. tagi=false;
    315. if (vl != null)
    316. {
    317. if (vl[0] != “”)
    318. {
    319. res += “@user”;
    320. //res += vl[0];
    321. tagi = true;
    322. }
    323. if (vl[1] != “”)
    324. {
    325. if (tagi)
    326. res+=” , “;
    327. res+=”@pass”;
    328. //res += vl[1];
    329. tagi = true;
    330. }
    331. if (vl[2] != “”)
    332. {
    333. if (tagi)
    334. res+=” , “;
    335. res+=”@tel”;
    336. // res += vl[2];
    337. tagi = true;
    338. }
    339. if (vl[3] != “”)
    340. {
    341. if (tagi)
    342. res+=” , “;
    343. res+=”@mail”;
    344. //res += vl[1];
    345. }
    346. }
    347. res += ” )”;
    348. sqlcom.CommandText = res;
    349. if (vl != null)
    350. {
    351. if (vl[0] != “”)
    352. {
    353. sqlcom.Parameters.AddWithValue(“@user”, vl[0]);
    354. }
    355. if (vl[1] != “”)
    356. {
    357. sqlcom.Parameters.AddWithValue(“@pass”, vl[1]);
    358. }
    359. if (vl[2] != “”)
    360. {
    361. sqlcom.Parameters.AddWithValue(“@tel”, vl[2]);
    362. }
    363. if (vl[3] != “”)
    364. {
    365. sqlcom.Parameters.AddWithValue(“@mail”, vl[3]);
    366. }
    367. }
    368. int i = sqlcom.ExecuteNonQuery();
    369. if (i > 0)
    370. {
    371. MessageBox.Show(“插入成功,请浏览数据”);
    372. }
    373. else {
    374. MessageBox.Show(“插入失败,请检查原因”);
    375. }
    376. sqlcon.Close();
    377. break;
    378. case “更新数据”:
    379. res = “update [用户表] set”;
    380. string [] vlu=get_value();
    381. string[] whu = get_where();
    382. bool tagu=false;
    383. if (vlu != null)
    384. {
    385. if (vlu[0] != “”)
    386. {
    387. res += ” 用户名=”;
    388. res += ” @user2”;
    389. tagu = true;
    390. }
    391. if (vlu[1] != “”)
    392. {
    393. if (tagu)
    394. res+=” , “;
    395. res+=” 密码=”;
    396. res += ” @pass2”;
    397. tagu = true;
    398. }
    399. if (vlu[2] != “”)
    400. {
    401. if (tagu)
    402. res+=” , “;
    403. res+=” 电话=”;
    404. res += ” @tel2”;
    405. tagu = true;
    406. }
    407. if (vlu[3] != “”)
    408. {
    409. if (tagu)
    410. res+=” , “;
    411. res+=” 邮箱=”;
    412. res+=” @mail2”;
    413. }
    414. }
    415. tagu = false;
    416. if (whu != null)
    417. {
    418. res+=” where “;
    419. if (whu[0] != “”)
    420. {
    421. res += ” 用户名=@user “;
    422. //res+=wh[0];
    423. tagu = true;
    424. }
    425. if (whu[1] != “”)
    426. {
    427. if (tagu)
    428. res+=”and “;
    429. res+=” 密码=@pass “;
    430. //res+=wh[1];
    431. tagu = true;
    432. }
    433. if (whu[2] != “”)
    434. {
    435. if (tagu)
    436. res += ” and “;
    437. res+=” 电话=@tel “;
    438. //res+=wh[2];
    439. tagu = true;
    440. }
    441. if (whu[3] != “”)
    442. {
    443. if (tagu)
    444. res += ” and “;
    445. res+=” 邮箱=@mail “;
    446. // res+=wh[1];
    447. }
    448. }

    449. /
    450. *
    451. *
    452. */
    453. sqlcom.CommandText = res;
    454. if (vlu != null)
    455. {
    456. if (vlu[0] != “”)
    457. {
    458. sqlcom.Parameters.AddWithValue(“@user2”, vlu[0]);
    459. }
    460. if (vlu[1] != “”)
    461. {
    462. sqlcom.Parameters.AddWithValue(“@pass2”, vlu[1]);
    463. }
    464. if (vlu[2] != “”)
    465. {
    466. sqlcom.Parameters.AddWithValue(“@tel2”, vlu[2]);
    467. }
    468. if (vlu[3] != “”)
    469. {
    470. sqlcom.Parameters.AddWithValue(“@mail2”, vlu[3]);
    471. }
    472. }
    473. if (whu != null)
    474. {
    475. if (whu[0] != “”)
    476. {
    477. sqlcom.Parameters.AddWithValue(“@user”, whu[0]);
    478. }
    479. if (whu[1] != “”)
    480. {
    481. sqlcom.Parameters.AddWithValue(“@pass”, whu[1]);
    482. }
    483. if (whu[2] != “”)
    484. {
    485. sqlcom.Parameters.AddWithValue(“@tel”, whu[2]);
    486. }
    487. if (whu[3] != “”)
    488. {
    489. sqlcom.Parameters.AddWithValue(“@mail”, whu[3]);
    490. }
    491. }
    492. int pcount = sqlcom.ExecuteNonQuery();
    493. if (pcount > 0)
    494. {
    495. MessageBox.Show(“更新成功,请浏览数据”);
    496. }
    497. else {
    498. MessageBox.Show(“更新失败,请检查原因”);
    499. }
    500. sqlcon.Close();
    501. break;
    502. case “删除数据”:
    503. res = “delete from [用户表]”;
    504. string [] whd=get_where();
    505. bool tagd=false;
    506. if (whd != null)
    507. {
    508. res+=” where “;
    509. if (whd[0] != “”)
    510. {
    511. res += ” 用户名=@user “;
    512. //res+=wh[0];
    513. tagd = true;
    514. }
    515. if (whd[1] != “”)
    516. {
    517. if (tagd)
    518. res+=”and “;
    519. res+=” 密码=@pass “;
    520. //res+=wh[1];
    521. tagd = true;
    522. }
    523. if (whd[2] != “”)
    524. {
    525. if (tagd)
    526. res += ” and “;
    527. res+=” 电话=@tel “;
    528. //res+=wh[2];
    529. tagd = true;
    530. }
    531. if (whd[3] != “”)
    532. {
    533. if (tagd)
    534. res += ” and “;
    535. res+=” 邮箱=@mail “;
    536. // res+=wh[1];
    537. }
    538. sqlcom.CommandText = res;
    539. if (whd != null)
    540. {
    541. if (whd[0] != “”)
    542. {
    543. sqlcom.Parameters.AddWithValue(“@user”, whd[0]);
    544. }
    545. if (whd[1] != null)
    546. {
    547. sqlcom.Parameters.AddWithValue(“@pass”, whd[1]);
    548. }
    549. if (whd[2] != null)
    550. {
    551. sqlcom.Parameters.AddWithValue(“@tel”, whd[2]);
    552. }
    553. if (whd[3] != null)
    554. {
    555. sqlcom.Parameters.AddWithValue(“@mail”, whd[3]);
    556. }
    557. }
    558. try
    559. {
    560. int rc=sqlcom.ExecuteNonQuery();
    561. if (rc > 0)
    562. {
    563. MessageBox.Show(“数据删除成功,请浏览数据”);
    564. }
    565. else
    566. {
    567. MessageBox.Show(“数据删除失败,请检查原因”);
    568. }
    569. }
    570. catch (Exception ex)
    571. {
    572. MessageBox.Show(“出现数据查询错误,请重试” + ex.Message);
    573. }
    574. finally {
    575. sqlcon.Close();
    576. }
    577. }
    578. break;
    579. }
    580. return res;
    581. }
    582. private void button1_Click(object sender, EventArgs e)
    583. {
    584. init_controler();
    585. }

    586. private void button2_Click(object sender, EventArgs e)
    587. {
    588. if (comboBox1.SelectedItem == null)
    589. {
    590. MessageBox.Show(“请选择右上方的操作后再执行确认”);
    591. return;
    592. }
    593. execu_sql();
    594. init_controler();
    595. }
    596. }
    597. }

IT文库 » WINFORM数据库操作,有点像安装里面的SQLITE
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址