用C#进行WinForm开发对数据库的相关操作
1 class SQLHelper 2 { 3 public SqlConnection conn; 4 5 //<summary> 6 //链接、打开数据库 7 //</summary> 8 public void open() 9 { 10 string connectionStr = "server = ;database = ;uid = sa;pwd = "; 11 conn = new SqlConnection(connectionStr); 12 conn.Open(); 13 } 14 15 //<summary> 16 //关闭数据库 17 //</summary> 18 public void close() 19 { 20 conn.Dispose(); 21 conn.Close(); 22 conn = null; 23 } 24 25 //<summary> 26 //得到Command对象 27 //</summary> 28 public SqlCommand GetCommand(string sqlstring) 29 { 30 SqlCommand cmd = new SqlCommand(sqlstring,conn); 31 return cmd; 32 } 33 34 //<summary> 35 //输入SQL语句,得到DataReader对象 36 //</summary> 37 public SqlDataReader GetDataReader(string sqlstring) 38 { 39 open(); 40 SqlCommand cmd = new SqlCommand(sqlstring,conn); 41 SqlDataReader dr = cmd.ExecuteReader(); 42 return dr; 43 } 44 45 //<summary> 46 //输入SQL语句,得到DataSet对象 47 //</summary> 48 public DataSet GetDataSet(string sqlstring) 49 { 50 open(); 51 SqlCommand cmd = new SqlCommand(sqlstring,conn); 52 SqlDataAdapter sda = new SqlDataAdapter(); 53 sda.SelectCommand = cmd; 54 DataSet ds = new DataSet(); 55 //string tmpName = tableName.ToString(); 56 sda.Fill(ds); 57 close(); 58 59 return ds; 60 } 61 62 //<summary> 63 //输入SQL语句,得到DataTable对象 64 //</summary> 65 public DataTable GetDataTable(string sqlstring) 66 { 67 DataSet ds = GetDataSet(sqlstring); 68 DataTable dt = new DataTable(); 69 dt = ds.Tables[0]; 70 71 return dt; 72 } 73 74 //<summary> 75 //执行非查询命令SQl命令 76 //</summary> 77 public int ExecuteSQL(string sqlstring) 78 { 79 int count = -1; 80 open(); 81 try 82 { 83 SqlCommand cmd = new SqlCommand(sqlstring,conn); 84 count = cmd.ExecuteNonQuery(); 85 } 86 catch 87 { 88 count = -1; 89 } 90 finally 91 { 92 close(); 93 } 94 return count; 95 } 96 97 //<summary> 98 //输入SQL语句,检查数据表中是否有该数据信息 99 //</summary> 100 public int GetDataRow(string sqlstring) 101 { 102 int CountRow = 0; 103 open(); 104 SqlCommand cmd = new SqlCommand(sqlstring,conn); 105 106 SqlDataAdapter sda = new SqlDataAdapter(); 107 sda.SelectCommand = cmd; 108 DataSet ds = new DataSet(); 109 sda.Fill(ds); 110 ds.CaseSensitive = false; 111 CountRow = ds.Tables[0].Rows.Count; 112 close(); 113 114 return CountRow; 115 } 116 117 //<summary> 118 //获取单个值 119 //</summary> 120 public object GetScalar(string sqlstring) 121 { 122 open(); 123 SqlCommand cmd = new SqlCommand(sqlstring,conn); 124 object result = cmd.ExecuteScalar(); 125 close(); 126 127 return result; 128 } 129 130 //<summary> 131 //查询某个表的某列的属性数据,并形成列表 132 //</summary> 133 //<param name="sqlstring">查询SQL语句</param> 134 //<param name="m">第m列的属性,整数类型</param> 135 //<return>ArrayList类型数据,存储在ArrayList中的一组数据</return> 136 public ArrayList GetArrayList(string sqlstring,int m) 137 { 138 //创建Arraylist对象 139 ArrayList array = new ArrayList(); 140 SqlDataReader dr = GetDataReader(sqlstring); 141 while (dr.Read()) //遍历所有结果集 142 { 143 //取结果集索引的第m列的值并添加到ArrayList对象中 144 array.Add(dr.GetValue(m)); 145 } 146 return array; //返回ArrayList对象 147 } 148 149 //<summary> 150 //对整体数据集实时批量更新 151 //<summary> 152 //<param name="ds">DataSet</param> 153 //<param name="sqlstring">SQL语句</param> 154 //<param name="tableName">表名</param> 155 //<return>bool变量,表示是否修改成功</return> 156 public bool doupdate(DataSet ds, string sqlstring, string tableName) 157 { 158 bool isUpdateOk = false; 159 open(); 160 //强制资源清理;Using结束后隐式调用 161 //Disposable 162 using (SqlDataAdapter da = new SqlDataAdapter(sqlstring, conn)) 163 { 164 //数据库表一定要有主键列,否则此处无法通过 165 SqlCommandBuilder builder = new SqlCommandBuilder(da); 166 try 167 { 168 lock (this) 169 { 170 da.Update(ds,tableName); 171 isUpdateOk = true; 172 } 173 } 174 catch(SqlException ex) 175 { 176 MessageBox.Show(ex.ToString(),"错误提示"); 177 } 178 } 179 close(); 180 return isUpdateOk; 181 } 182 }