源码： C#操作SQL数据库 SQLDataReader/SQLDataAdapter/DataSet/DataTable

命令行运行的操作SQL数据库的工具。

 

Usage:

 

显示表中所有记录            SQLTool.exe /all 

按照id查询            SQLTool.exe /qid <id_str>

按照customer字段查询        SQLTool.exe /qcustomer <customer_str>

插入记录                SQLTool.exe /insert <id_str> <date_str> <price_str> <customer_str>

删除记录                SQLTool.exe /delete <id_str>

更新记录                SQLTool.exe /update <id_str> <date_str> <price_str> <customer_str>

 

 

本例前提：

存在一个数据库如下：

 

源码：

class Program { private string connStr = "Data Source=10.177.162.251;Initial Catalog =Orders;uid=sa;pwd=Pa$$w0rd"; private SqlConnection conn; private SqlCommand comm; static void Main(string[] args) { if (args.Length == 0) { Console.WriteLine("Usage:"); Console.WriteLine("Syntax: XXXXXXXXXXXXXXXXXXXXXXX"); return; } switch (args[0]) { case "/all": if (args.Length == 1) { Program p = new Program(); p.showAll(); } break; case "/qid": if (args.Length == 2) { Program p = new Program(); DataRowCollection rows = p.QueryById(args[1]); p.printRows(rows); } break; case "/qcustomer": if (args.Length == 2) { Program p = new Program(); DataRowCollection rows = p.QueryByCustomer(args[1]); p.printRows(rows); } break; case "/delete": if (args.Length == 2) { Program p = new Program(); p.DeleteById(args[1]); } break; case "/insert": if (args.Length == 5) { Program p = new Program(); p.InsertRow(args[1], args[2], args[3], args[4]); } break; case "/update": if (args.Length == 5) { Program p = new Program(); p.UpdateRow(args[1], args[2], args[3], args[4]); } break; default: break; } } //show all records in Orders_new table private void showAll() { conn = new SqlConnection(connStr); string commStr = "select * from Orders_new"; comm = new SqlCommand(commStr, conn); try { conn.Open(); SqlDataReader reader = comm.ExecuteReader(); if (reader.HasRows) { Console.WriteLine(); Console.WriteLine("/tid/tOrderDate/tOrderPrice/tCustomer"); int count = 0; while (reader.Read()) { Console.WriteLine("/t" + reader["id"] + "/t" + reader["OrderDate"] + "/t" + reader["OrderPrice"] + "/t" + reader["Customer"]); count++; } Console.WriteLine("/nReturned {0} records in total!", count); } } catch (Exception e) { MessageBox.Show(e.Message); } finally { conn.Close(); } } private DataRowCollection QueryById(string id_str) { conn = new SqlConnection(connStr); comm = new SqlCommand(); comm.Connection = conn; string commStr = "select * from Orders_new where id ='" + id_str + "'"; comm.CommandText = commStr; DataRowCollection ret = null; try { conn.Open(); SqlDataAdapter adapter = new SqlDataAdapter(comm); DataSet ds = new DataSet(); adapter.Fill(ds, "Orders_new"); DataTable table = ds.Tables["Orders_new"]; ret = table.Rows; } catch (SqlException se) { MessageBox.Show(se.Message); } catch (InvalidOperationException ie) { MessageBox.Show(ie.Message); } catch (ArgumentException ae) { MessageBox.Show(ae.Message); } finally { conn.Close(); } return ret; } private DataRowCollection QueryByCustomer(string Customer_str) { conn = new SqlConnection(connStr); comm = new SqlCommand(); comm.Connection = conn; string commStr = "select * from Orders_new where Customer ='" + Customer_str + "'"; comm.CommandText = commStr; DataRowCollection ret = null; try { conn.Open(); SqlDataAdapter adapter = new SqlDataAdapter(commStr, conn); DataSet ds = new DataSet(); adapter.Fill(ds, "Orders"); DataTable dt = ds.Tables["Orders"]; ret = dt.Rows; } catch (ArgumentException ae) { MessageBox.Show(ae.Message); } catch (SqlException se) { MessageBox.Show(se.Message); } catch (InvalidOperationException ie) { MessageBox.Show(ie.Message); } finally { conn.Close(); } return ret; } private void DeleteById(string id_str) { if (string.IsNullOrWhiteSpace(id_str)) { return; } conn = new SqlConnection(connStr); comm = new SqlCommand(); comm.Connection = conn; string commStr = "delete from Orders_new where id ='" + id_str + "'"; comm.CommandText = commStr; try { conn.Open(); int affectedRows = comm.ExecuteNonQuery(); if (affectedRows > 0) { MessageBox.Show("Deleted successfully!", "Congratulations", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { MessageBox.Show("Delete failed: the specified record does not exist!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error); } } catch (ArgumentException ae) { MessageBox.Show(ae.Message); } catch (SqlException se) { MessageBox.Show(se.Message); } catch (InvalidOperationException ie) { MessageBox.Show(ie.Message); } finally { conn.Close(); } } private void InsertRow(string id_str, string date_Str, string price_Str, string customer_str) { if (string.IsNullOrWhiteSpace(id_str)) { return; } conn = new SqlConnection(connStr); comm = new SqlCommand(); comm.Connection = conn; string commStr = "insert into Orders_new values('" + id_str + "','" + date_Str + "','" + price_Str + "','" + customer_str + "')"; comm.CommandText = commStr; try { conn.Open(); int affectedRows = comm.ExecuteNonQuery(); if (affectedRows == 1) { MessageBox.Show("1 record inserted successfully", "Congratulations", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { MessageBox.Show("The number of affected rows is not 1", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } catch (ArgumentException ae) { MessageBox.Show(ae.Message); } catch (SqlException se) { MessageBox.Show(se.Message); } catch (InvalidOperationException ie) { MessageBox.Show(ie.Message); } finally { conn.Close(); } } private void UpdateRow(string id_str, string date_Str, string price_Str, string customer_str) { conn = new SqlConnection(connStr); comm = new SqlCommand(); comm.Connection = conn; string commStr = "update Orders_new set OrderDate = '" + date_Str + "',OrderPrice ='" + price_Str + "',Customer='" + customer_str + "' where id =" + id_str; Form f = new Form(); TextBox tb = new TextBox(); tb.Text = commStr; tb.Width = f.Width; tb.Height = f.Height; tb.Parent = f; f.ShowDialog(); comm.CommandText = commStr; try { conn.Open(); int affectedRows = comm.ExecuteNonQuery(); if (affectedRows == 1) { MessageBox.Show("Success"); } else { MessageBox.Show("Error"); } } catch (ArgumentException ae) { MessageBox.Show(ae.Message); } catch (SqlException se) { MessageBox.Show(se.Message); } catch (InvalidOperationException ie) { MessageBox.Show(ie.Message); } finally { conn.Close(); } } private void printRows(DataRowCollection rows) { foreach (DataRow row in rows) { Console.WriteLine(); Console.WriteLine("/tid/tOrderDate/tOrderPrice/tCustomer"); Console.WriteLine("/t" + row["id"] + "/t" + row["OrderDate"] + "/t" + row["OrderPrice"] + "/t" + row["Customer"]); } Console.WriteLine(); Console.WriteLine("Returned {0} records in total!", rows.Count); } }

 

 

效果：