01.using System;
02.using System.Collections;
03.using System.Collections.Generic;
04.using System.ComponentModel;
05.using System.Data;
06.using System.Linq;
07.using System.Text;
08.using System.Windows.Forms;
09.using System.Data.SqlClient;
10.
11.namespace SampleSmartCardSDK
12.{
13. public partial class frmReIndexCustomerID : Form
14. {
15. private SqlConnection objConn;
16. private SqlCommand objCmd;
17. private SqlDataAdapter _dtAdapter;
18. private DataSet _dtSet;
19. private String strConnString, strSQL;
20.
21. public frmReIndexCustomerID()
22. {
23. InitializeComponent();
24. }
25.
26. private void ReIndexOfCustomerID()
27. {
28. strConnString = "Server = localhost; Uid = sa; PASSWORD = password; database = Customer;" +
29. "Max Pool Size = 400; Connect Timeout = 600;";
30. objConn = new SqlConnection(strConnString);
31. objConn.Open();
32.
33. strSQL = "SELECT CustomerID FROM CustomerInfo ORDER BY CustomerID";
34.
35. _dtAdapter = new SqlDataAdapter(strSQL, objConn);
36. _dtSet = new DataSet();
37. _dtAdapter.Fill(_dtSet, "CustomerInfo");
38.
39. List<string> customerID = new List<string>();
40. foreach (DataRow dtRow in _dtSet.Tables["CustomerInfo"].Rows)
41. {
42. customerID.Add(dtRow["CustomerID"].ToString());
43. }
44.
45. for (int i = 0; i < customerID.Count; i++)
46. {
47. string prefix = "CUS";
48. string posfix = prefix + (i + 1).ToString("0######");
49.
50. objConn = new SqlConnection(strConnString);
51. objCmd = new SqlCommand("UPDATE CustomerInfo SET CustomerID = @customerIDNew" +
52. " WHERE CustomerID = '" + customerID[i] + "'", objConn);
53. objCmd.Parameters.AddWithValue("@customerIDNew", posfix);
54. try
55. {
56. objConn.Open();
57. objCmd.ExecuteNonQuery();
58. }
59. catch (Exception ex)
60. {
61. MessageBox.Show(ex.Message);
62. }
63. }
64. MessageBox.Show("ทำรายการเสร็จเรียบร้อยแล้ว", "จัดเรียงรหัสลูกค้า");
65. Application.Exit();
66. }
67.
68. private void frmReIndexCustomerID_Load(object sender, EventArgs e)
69. {
70. ReIndexOfCustomerID();
71. }
72. }
73.}
