热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

【WPF学习笔记】之如何通过后台C#代码添加(增/删/改按钮)实现对SQLServer数据库数据的更改...

首先,需要连接SQLServer数据库的服务器名称server、数据库名database、数据库用户名uid以及密码pwd,如下图:然后需

首先,需要连接SQLServer数据库的服务器名称server、数据库名database、数据库用户名uid以及密码pwd,如下图:

 然后需要以下数据库SQL代码段,还有一个myHelper.cs代码,粘贴复制即可:

DbHelperSQL.cs代码:

注意要更改红框中的内容

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;namespace MTBug.sqlHelper
{
///

/// 数据访问抽象基础类/// Copyright (C) Maticsoft /// public abstract class DbHelperSQL{//数据库连接字符串(web.config来配置),多数据库可使用DbHelperSQLP来实现.//public static string connectionString = @"server=localhost;database=MTBugList;uid=sa;pwd=123";public static string connectionString = @"server=localhost;database=MTBugList;uid=sa;pwd=aaaaaa";public DbHelperSQL(){//connectionString = @"server=" + System.Net.Dns.GetHostName() + ";database=TianZhong;uid=sa;pwd=" + myHelper.getXMLValue("dbUserPW");connectionString = @"server=" + myHelper.getXMLValue("dbAddr") + ";database=MTBugList;uid=sa;pwd=" + myHelper.getXMLValue("dbUserPW");}#region 公用方法/// /// 判断是否存在某表的某个字段/// /// 表名称/// 列名称/// 是否存在public static bool ColumnExists(string tableName, string columnName){string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";object res = GetSingle(sql);if (res == null){return false;}return Convert.ToInt32(res) > 0;}public static int GetMaxID(string FieldName, string TableName){string strsql = "select max(" + FieldName + ")+1 from " + TableName;object obj = GetSingle(strsql);if (obj == null){return 1;}else{return int.Parse(obj.ToString());}}public static bool Exists(string strSql){object obj = GetSingle(strSql);int cmdresult;if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){cmdresult = 0;}else{cmdresult = int.Parse(obj.ToString()); //也可能=0
}if (cmdresult == 0){return false;}else{return true;}}/// /// 表是否存在/// /// /// public static bool TabExists(string TableName){string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";//string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";object obj = GetSingle(strsql);int cmdresult;if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){cmdresult = 0;}else{cmdresult = int.Parse(obj.ToString());}if (cmdresult == 0){return false;}else{return true;}}public static bool Exists(string strSql, params SqlParameter[] cmdParms){object obj = GetSingle(strSql, cmdParms);int cmdresult;if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){cmdresult = 0;}else{cmdresult = int.Parse(obj.ToString());}if (cmdresult == 0){return false;}else{return true;}}#endregion#region 执行简单SQL语句/// /// 执行SQL语句,返回影响的记录数/// /// SQL语句/// 影响的记录数public static int ExecuteSql(string SQLString){using (SqlConnection connection = new SqlConnection(connectionString)){using (SqlCommand cmd = new SqlCommand(SQLString, connection)){try{connection.Open();int rows = cmd.ExecuteNonQuery();return rows;}catch (System.Data.SqlClient.SqlException e){connection.Close();throw e;}}}}public static int ExecuteSqlByTime(string SQLString, int Times){using (SqlConnection connection = new SqlConnection(connectionString)){using (SqlCommand cmd = new SqlCommand(SQLString, connection)){try{connection.Open();cmd.CommandTimeout = Times;int rows = cmd.ExecuteNonQuery();return rows;}catch (System.Data.SqlClient.SqlException e){connection.Close();throw e;}}}}/// /// 执行多条SQL语句,实现数据库事务。/// /// 多条SQL语句 public static int ExecuteSqlTran(List SQLStringList){using (SqlConnection conn = new SqlConnection(connectionString)){conn.Open();SqlCommand cmd = new SqlCommand();cmd.Connection = conn;SqlTransaction tx = conn.BeginTransaction();cmd.Transaction = tx;try{int count = 0;for (int n = 0; n ){string strsql = SQLStringList[n];if (strsql.Trim().Length > 1){cmd.CommandText = strsql;count += cmd.ExecuteNonQuery();}}tx.Commit();return count;}catch{tx.Rollback();return 0;}}}/// /// 执行带一个存储过程参数的的SQL语句。/// /// SQL语句/// 参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加/// 影响的记录数public static int ExecuteSql(string SQLString, string content){using (SqlConnection connection = new SqlConnection(connectionString)){SqlCommand cmd = new SqlCommand(SQLString, connection);System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);myParameter.Value = content;cmd.Parameters.Add(myParameter);try{connection.Open();int rows = cmd.ExecuteNonQuery();return rows;}catch (System.Data.SqlClient.SqlException e){throw e;}finally{cmd.Dispose();connection.Close();}}}/// /// 执行带一个存储过程参数的的SQL语句。/// /// SQL语句/// 参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加/// 影响的记录数public static object ExecuteSqlGet(string SQLString, string content){using (SqlConnection connection = new SqlConnection(connectionString)){SqlCommand cmd = new SqlCommand(SQLString, connection);System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);myParameter.Value = content;cmd.Parameters.Add(myParameter);try{connection.Open();object obj = cmd.ExecuteScalar();if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){return null;}else{return obj;}}catch (System.Data.SqlClient.SqlException e){throw e;}finally{cmd.Dispose();connection.Close();}}}/// /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)/// /// SQL语句/// 图像字节,数据库的字段类型为image的情况/// 影响的记录数public static int ExecuteSqlInsertImg(string strSQL, byte[] fs){using (SqlConnection connection = new SqlConnection(connectionString)){SqlCommand cmd = new SqlCommand(strSQL, connection);System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);myParameter.Value = fs;cmd.Parameters.Add(myParameter);try{connection.Open();int rows = cmd.ExecuteNonQuery();return rows;}catch (System.Data.SqlClient.SqlException e){throw e;}finally{cmd.Dispose();connection.Close();}}}/// /// 执行一条计算查询结果语句,返回查询结果(object)。/// /// 计算查询结果语句/// 查询结果(object)public static object GetSingle(string SQLString){using (SqlConnection connection = new SqlConnection(connectionString)){using (SqlCommand cmd = new SqlCommand(SQLString, connection)){try{connection.Open();object obj = cmd.ExecuteScalar();if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){return null;}else{return obj;}}catch (System.Data.SqlClient.SqlException e){connection.Close();throw e;}}}}public static object GetSingle(string SQLString, int Times){using (SqlConnection connection = new SqlConnection(connectionString)){using (SqlCommand cmd = new SqlCommand(SQLString, connection)){try{connection.Open();cmd.CommandTimeout = Times;object obj = cmd.ExecuteScalar();if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){return null;}else{return obj;}}catch (System.Data.SqlClient.SqlException e){connection.Close();throw e;}}}}/// /// 执行查询语句,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )/// /// 查询语句/// SqlDataReaderpublic static SqlDataReader ExecuteReader(string strSQL){SqlConnection connection = new SqlConnection(connectionString);SqlCommand cmd = new SqlCommand(strSQL, connection);try{connection.Open();SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);return myReader;}catch (System.Data.SqlClient.SqlException e){throw e;}}/// /// 执行查询语句,返回DataSet/// /// 查询语句/// DataSetpublic static DataSet Query(string SQLString){using (SqlConnection connection = new SqlConnection(connectionString)){DataSet ds = new DataSet();try{connection.Open();SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);command.Fill(ds, "ds");}catch (System.Data.SqlClient.SqlException ex){throw new Exception(ex.Message);}return ds;}}public static DataSet Query(string SQLString, int Times){using (SqlConnection connection = new SqlConnection(connectionString)){DataSet ds = new DataSet();try{connection.Open();SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);command.SelectCommand.CommandTimeout = Times;command.Fill(ds, "ds");}catch (System.Data.SqlClient.SqlException ex){throw new Exception(ex.Message);}return ds;}}#endregion#region 执行带参数的SQL语句/// /// 执行SQL语句,返回影响的记录数/// /// SQL语句/// 影响的记录数public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms){using (SqlConnection connection = new SqlConnection(connectionString)){using (SqlCommand cmd = new SqlCommand()){try{PrepareCommand(cmd, connection, null, SQLString, cmdParms);int rows = cmd.ExecuteNonQuery();cmd.Parameters.Clear();return rows;}catch (System.Data.SqlClient.SqlException e){throw e;}}}}/// /// 执行多条SQL语句,实现数据库事务。/// /// SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])public static void ExecuteSqlTran(Hashtable SQLStringList){using (SqlConnection conn = new SqlConnection(connectionString)){conn.Open();using (SqlTransaction trans = conn.BeginTransaction()){SqlCommand cmd = new SqlCommand();try{//循环foreach (DictionaryEntry myDE in SQLStringList){string cmdText = myDE.Key.ToString();SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;PrepareCommand(cmd, conn, trans, cmdText, cmdParms);int val = cmd.ExecuteNonQuery();cmd.Parameters.Clear();}trans.Commit();}catch{trans.Rollback();throw;}}}}/// /// 执行多条SQL语句,实现数据库事务。/// /// SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])public static int ExecuteSqlTran(System.Collections.Generic.List cmdList){using (SqlConnection conn = new SqlConnection(connectionString)){conn.Open();using (SqlTransaction trans = conn.BeginTransaction()){SqlCommand cmd = new SqlCommand();try{int count = 0;//循环foreach (CommandInfo myDE in cmdList){string cmdText = myDE.CommandText;SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;PrepareCommand(cmd, conn, trans, cmdText, cmdParms);if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine){if (myDE.CommandText.ToLower().IndexOf("count(") == -1){trans.Rollback();return 0;}object obj = cmd.ExecuteScalar();bool isHave = false;if (obj == null && obj == DBNull.Value){isHave = false;}isHave = Convert.ToInt32(obj) > 0;if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave){trans.Rollback();return 0;}if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave){trans.Rollback();return 0;}continue;}int val = cmd.ExecuteNonQuery();count += val;if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0){trans.Rollback();return 0;}cmd.Parameters.Clear();}trans.Commit();return count;}catch{trans.Rollback();throw;}}}}/// /// 执行多条SQL语句,实现数据库事务。/// /// SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List SQLStringList){using (SqlConnection conn = new SqlConnection(connectionString)){conn.Open();using (SqlTransaction trans = conn.BeginTransaction()){SqlCommand cmd = new SqlCommand();try{int indentity = 0;//循环foreach (CommandInfo myDE in SQLStringList){string cmdText = myDE.CommandText;SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;foreach (SqlParameter q in cmdParms){if (q.Direction == ParameterDirection.InputOutput){q.Value = indentity;}}PrepareCommand(cmd, conn, trans, cmdText, cmdParms);int val = cmd.ExecuteNonQuery();foreach (SqlParameter q in cmdParms){if (q.Direction == ParameterDirection.Output){indentity = Convert.ToInt32(q.Value);}}cmd.Parameters.Clear();}trans.Commit();}catch{trans.Rollback();throw;}}}}/// /// 执行多条SQL语句,实现数据库事务。/// /// SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList){using (SqlConnection conn = new SqlConnection(connectionString)){conn.Open();using (SqlTransaction trans = conn.BeginTransaction()){SqlCommand cmd = new SqlCommand();try{int indentity = 0;//循环foreach (DictionaryEntry myDE in SQLStringList){string cmdText = myDE.Key.ToString();SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;foreach (SqlParameter q in cmdParms){if (q.Direction == ParameterDirection.InputOutput){q.Value = indentity;}}PrepareCommand(cmd, conn, trans, cmdText, cmdParms);int val = cmd.ExecuteNonQuery();foreach (SqlParameter q in cmdParms){if (q.Direction == ParameterDirection.Output){indentity = Convert.ToInt32(q.Value);}}cmd.Parameters.Clear();}trans.Commit();}catch{trans.Rollback();throw;}}}}/// /// 执行一条计算查询结果语句,返回查询结果(object)。/// /// 计算查询结果语句/// 查询结果(object)public static object GetSingle(string SQLString, params SqlParameter[] cmdParms){using (SqlConnection connection = new SqlConnection(connectionString)){using (SqlCommand cmd = new SqlCommand()){try{PrepareCommand(cmd, connection, null, SQLString, cmdParms);object obj = cmd.ExecuteScalar();cmd.Parameters.Clear();if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){return null;}else{return obj;}}catch (Exception e){Console.WriteLine(e.Message);return null;//throw e;
}}}}/// /// 执行查询语句,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )/// /// 查询语句/// SqlDataReaderpublic static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms){SqlConnection connection = new SqlConnection(connectionString);SqlCommand cmd = new SqlCommand();try{PrepareCommand(cmd, connection, null, SQLString, cmdParms);SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);cmd.Parameters.Clear();return myReader;}catch (System.Data.SqlClient.SqlException e){throw e;}// finally// {// cmd.Dispose();// connection.Close();// }
}/// /// 执行查询语句,返回DataSet/// /// 查询语句/// DataSetpublic static DataSet Query(string SQLString, params SqlParameter[] cmdParms){using (SqlConnection connection = new SqlConnection(connectionString)){SqlCommand cmd = new SqlCommand();PrepareCommand(cmd, connection, null, SQLString, cmdParms);using (SqlDataAdapter da = new SqlDataAdapter(cmd)){DataSet ds = new DataSet();try{da.Fill(ds, "ds");cmd.Parameters.Clear();}catch (System.Data.SqlClient.SqlException ex){throw new Exception(ex.Message);}return ds;}}}private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms){if (conn.State != ConnectionState.Open)conn.Open();cmd.Connection = conn;cmd.CommandText = cmdText;if (trans != null)cmd.Transaction = trans;cmd.CommandType = CommandType.Text;//cmdType;if (cmdParms != null){foreach (SqlParameter parameter in cmdParms){if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&(parameter.Value == null)){parameter.Value = DBNull.Value;}cmd.Parameters.Add(parameter);}}}#endregion#region 存储过程操作/// /// 执行存储过程,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )/// /// 存储过程名/// 存储过程参数/// SqlDataReaderpublic static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters){SqlConnection connection = new SqlConnection(connectionString);SqlDataReader returnReader;connection.Open();SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);command.CommandType = CommandType.StoredProcedure;returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);return returnReader;}/// /// 执行存储过程/// /// 存储过程名/// 存储过程参数/// DataSet结果中的表名/// DataSetpublic static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName){using (SqlConnection connection = new SqlConnection(connectionString)){DataSet dataSet = new DataSet();connection.Open();SqlDataAdapter sqlDA = new SqlDataAdapter();sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);sqlDA.Fill(dataSet, tableName);connection.Close();return dataSet;}}public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times){using (SqlConnection connection = new SqlConnection(connectionString)){DataSet dataSet = new DataSet();connection.Open();SqlDataAdapter sqlDA = new SqlDataAdapter();sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);sqlDA.SelectCommand.CommandTimeout = Times;sqlDA.Fill(dataSet, tableName);connection.Close();return dataSet;}}/// /// 构建 SqlCommand 对象(用来返回一个结果集,而不是一个整数值)/// /// 数据库连接/// 存储过程名/// 存储过程参数/// SqlCommandprivate static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters){SqlCommand command = new SqlCommand(storedProcName, connection);command.CommandType = CommandType.StoredProcedure;foreach (SqlParameter parameter in parameters){if (parameter != null){// 检查未分配值的输出参数,将其分配以DBNull.Value.if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&(parameter.Value == null)){parameter.Value = DBNull.Value;}command.Parameters.Add(parameter);}}return command;}/// /// 执行存储过程,返回影响的行数 /// /// 存储过程名/// 存储过程参数/// 影响的行数/// public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected){using (SqlConnection connection = new SqlConnection(connectionString)){int result;connection.Open();SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);rowsAffected = command.ExecuteNonQuery();result = (int)command.Parameters["ReturnValue"].Value;//Connection.Close();return result;}}/// /// 创建 SqlCommand 对象实例(用来返回一个整数值) /// /// 存储过程名/// 存储过程参数/// SqlCommand 对象实例private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters){SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);command.Parameters.Add(new SqlParameter("ReturnValue",SqlDbType.Int, 4, ParameterDirection.ReturnValue,false, 0, 0, string.Empty, DataRowVersion.Default, null));return command;}#endregion}
}
View Code

CommandInfo.cs代码:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;namespace MTBug.sqlHelper
{
public enum EffentNextType{///

/// 对其他语句无任何影响 ///
None,/// /// 当前语句必须为"select count(1) from .."格式,如果存在则继续执行,不存在回滚事务///
WhenHaveContine,/// /// 当前语句必须为"select count(1) from .."格式,如果不存在则继续执行,存在回滚事务///
WhenNoHaveContine,/// /// 当前语句影响到的行数必须大于0,否则回滚事务///
ExcuteEffectRows,/// /// 引发事件-当前语句必须为"select count(1) from .."格式,如果不存在则继续执行,存在回滚事务///
SolicitationEvent}public class CommandInfo{public object ShareObject = null;public object OriginalData = null;event EventHandler _solicitationEvent;public event EventHandler SolicitationEvent{add{_solicitationEvent += value;}remove{_solicitationEvent -= value;}}public void OnSolicitationEvent(){if (_solicitationEvent != null){_solicitationEvent(this, new EventArgs());}}public string CommandText;public System.Data.Common.DbParameter[] Parameters;public EffentNextType EffentNextType = EffentNextType.None;public CommandInfo(){}public CommandInfo(string sqlText, SqlParameter[] para){this.CommandText = sqlText;this.Parameters = para;}public CommandInfo(string sqlText, SqlParameter[] para, EffentNextType type){this.CommandText = sqlText;this.Parameters = para;this.EffentNextType = type;}}
}
View Code

myHelper.cs代码:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.IO;namespace MTBug
{
public static class myHelper{public delegate void ShowWarnMessage(string _str);public static event ShowWarnMessage showWarnMessage;public static string xmlPath = System.Environment.CurrentDirectory + "\\Resources\\Config.xml";public static void mySendMessageToMainWindow(string _str){if (showWarnMessage != null){showWarnMessage(_str);}}public static string getXMLValue(string eleName){try{XmlDocument xmlDoc1 = new XmlDocument();xmlDoc1.Load(xmlPath);var root = xmlDoc1.DocumentElement;XmlNode nodes = xmlDoc1.SelectSingleNode("//" + eleName);if (nodes != null){xmlDoc1.Save(xmlPath);return nodes.InnerText.ToString();}else{XmlElement xe1 = xmlDoc1.CreateElement(eleName);//创建一个节点 xe1.InnerText = "";root.AppendChild(xe1);xmlDoc1.Save(xmlPath);return "";}}catch (Exception tt){myHelper.errorLogAdd(tt.Message);return "";}}public static void errorLogAdd(string _str){try{_str = DateTime.Now.ToString("yyyyMMddhhmmss") + "-" + _str;if (System.IO.File.Exists("C://errorLog.txt")){StreamWriter sw = new StreamWriter("C://errorLog.txt", true, Encoding.GetEncoding("gb2312"));sw.WriteLine(_str);sw.Flush();sw.Close();}else{FileStream fs1 = new FileStream("C://errorLog.txt", FileMode.Create, FileAccess.Write);//创建
StreamWriter sw = new StreamWriter(fs1);sw.WriteLine(_str);sw.Flush();sw.Close();fs1.Close();}}catch { }}}
}

View Code

 

完成以上步骤之后,在MainWindow.xaml中添加增、删、改三个按钮:

<Canvas x:Name&#61;"c_sql" Width&#61;"500" Height&#61;"150" Canvas.Left&#61;"50" Canvas.Top&#61;"800" Background&#61;"SkyBlue"><Label x:Name&#61;"label1" Height&#61;"30" Width&#61;"120" Content&#61;"数据库SQL更改" FontFamily&#61;"Berlin Sans FB" FontSize&#61;"16"/><Button Canvas.Left&#61;"50" Canvas.Top&#61;"50" Content&#61;"添加" Height&#61;"60" Name&#61;"button1" Width&#61;"100" Click&#61;"button1_Click" FontSize&#61;"20"/><Button Canvas.Left&#61;"200" Canvas.Top&#61;"50" Content&#61;"删除" Height&#61;"60" Name&#61;"button2" Width&#61;"100" Click&#61;"button2_Click" FontSize&#61;"20"/><Button Canvas.Left&#61;"350" Canvas.Top&#61;"50" Content&#61;"更新" Height&#61;"60" Name&#61;"button3" Width&#61;"100" Click&#61;"button3_Click" FontSize&#61;"20"/>Canvas>

项目中新建一个类test.cs&#xff0c;代码如下&#xff1a;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;namespace MTBug.sqlHelper
{[Serializable]
public partial class test{public test(){}#region Modelprivate int _ids;private string _name;public int ids{set{ _ids &#61; value;}get{ return _ids;}}public string name{set{ _name &#61; value;}get{ return _name;}}#endregion Model#region Model///

/// 增加一条数据/// public int Add(int ids, string username){////string _str &#61; "insert into [test] (ids,name) values (" &#43; ids.ToString() &#43; "," &#43; "&#39;"&#43;username.ToString()&#43;"&#39;" &#43; ");";StringBuilder strSql &#61; new StringBuilder();strSql.Append("insert into [test] (");strSql.Append(" ids,name) ");strSql.Append(" values (");strSql.Append(" &#64;ids,&#64;name)");SqlParameter[] parameters &#61; {new SqlParameter("&#64;ids", SqlDbType.Int,4),new SqlParameter("&#64;name", SqlDbType.VarChar,20)};parameters[0].Value &#61; ids;parameters[1].Value &#61; username;//StringBuilder strSql &#61; new StringBuilder();//strSql.Append("insert into [test] (");//strSql.Append(" ids,name) values ( ");//strSql.Append("&#64;ids,&#64;name)");//SqlParameter[] parameters &#61; {// new SqlParameter("&#64;ids", SqlDbType.Int,4),// new SqlParameter("&#64;name", SqlDbType.VarChar,20)};//parameters[0].Value &#61; ids;//parameters[1].Value &#61; username;object obj &#61; DbHelperSQL.GetSingle(strSql.ToString(), parameters);if (obj &#61;&#61; null){return 0;}else{return Convert.ToInt32(obj);}}/// /// 删除一条数据/// public bool Delete(int ids){StringBuilder strSql &#61; new StringBuilder();strSql.Append("delete from [test] ");strSql.Append(" where ids&#61;" &#43; ids.ToString());int rows &#61; DbHelperSQL.ExecuteSql(strSql.ToString());if (rows > 0){return true;}else{return false;}}/// /// 更新一条数据/// public bool Update(int ids, string username){StringBuilder strSql &#61; new StringBuilder();strSql.Append("update [test] set ");strSql.Append("name&#61;&#64;name ");strSql.Append(" where ids&#61;&#64;ids ");SqlParameter[] parameters &#61; {new SqlParameter("&#64;ids", SqlDbType.Int,4),new SqlParameter("&#64;name", SqlDbType.VarChar,50)};parameters[0].Value &#61; ids;parameters[1].Value &#61; username;int rows &#61; DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);if (rows > 0){return true;}else{return false;}}#endregion Model}
}
View Code

test代码中定义如何对数据库进行增删改数据的方法。

最后&#xff0c;在MainWindow.xaml.cs后台代码中设置&#xff0c;三个按钮的点击事件&#xff1a;

private void button1_Click(object sender, RoutedEventArgs e){///点击往test表里随机插入一条数据 ///
test _u &#61; new test();int _ds &#61; _u.Add(2, "你好");}private void button2_Click(object sender, RoutedEventArgs e){test _u &#61; new test();bool _ds &#61; _u.Delete(2);}private void button3_Click(object sender, RoutedEventArgs e){test _u &#61; new test();bool _ds &#61; _u.Update(2, "欧文");}

 

查看数据库的表test&#xff0c;数据就可以增删改了。

 &#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;

出错地点总结&#xff1a;

①增删改数据库的方法类型要与相对应的方法类型一致&#xff1a;

对比&#xff1a;

 

②传递的参数要对应&#xff1a;

 

③编写sql语句要正确&#xff0c;尤其字符串要加上&#xff0c;单引号‘’。

例&#xff1a;insert into [MTBugList].[dbo].[test] (ids,name) values (3,&#39;你好&#39;); 

 

④有两种C#sql语句的写法&#xff1a;

第一种&#xff1a;

///

/// 增加一条数据/// public int Add(int ids, string username){string _str &#61; "insert into [test] (ids,name) values (" &#43; ids.ToString() &#43; "," &#43; "&#39;" &#43; username.ToString() &#43; "&#39;" &#43; ");";object obj &#61; DbHelperSQL.GetSingle(_str.ToString());if (obj &#61;&#61; null){return 0;}else{return Convert.ToInt32(obj);}}

第二种&#xff1a;

///

/// 增加一条数据/// public int Add(int ids, string username){StringBuilder strSql &#61; new StringBuilder();strSql.Append("insert into [test] (");strSql.Append(" ids,name) ");strSql.Append(" values (");strSql.Append(" &#64;ids,&#64;name)");SqlParameter[] parameters &#61; {new SqlParameter("&#64;ids", SqlDbType.Int,4),new SqlParameter("&#64;name", SqlDbType.VarChar,20)};parameters[0].Value &#61; ids;parameters[1].Value &#61; username;object obj &#61; DbHelperSQL.GetSingle(strSql.ToString(), parameters);if (obj &#61;&#61; null){return 0;}else{return Convert.ToInt32(obj);}}

 

⑤注意④中含多个字段时&#xff0c;parameters[&#xff1f;].Value要随着更改。

 

转:https://www.cnblogs.com/Owen-ET/p/5999654.html



推荐阅读
  • 由二叉树到贪心算法
    二叉树很重要树是数据结构中的重中之重,尤其以各类二叉树为学习的难点。单就面试而言,在 ... [详细]
  • 深入解析SpringMVC核心组件:DispatcherServlet的工作原理
    本文详细探讨了SpringMVC的核心组件——DispatcherServlet的运作机制,旨在帮助有一定Java和Spring基础的开发人员理解HTTP请求是如何被映射到Controller并执行的。文章将解答以下问题:1. HTTP请求如何映射到Controller;2. Controller是如何被执行的。 ... [详细]
  • 本文详细介绍了优化DB2数据库性能的多种方法,涵盖统计信息更新、缓冲池调整、日志缓冲区配置、应用程序堆大小设置、排序堆参数调整、代理程序管理、锁机制优化、活动应用程序限制、页清除程序配置、I/O服务器数量设定以及编入组提交数调整等方面。通过这些技术手段,可以显著提升数据库的运行效率和响应速度。 ... [详细]
  • 并发编程 12—— 任务取消与关闭 之 shutdownNow 的局限性
    Java并发编程实践目录并发编程01——ThreadLocal并发编程02——ConcurrentHashMap并发编程03——阻塞队列和生产者-消费者模式并发编程04——闭锁Co ... [详细]
  • 2018-2019学年第六周《Java数据结构与算法》学习总结
    本文总结了2018-2019学年第六周在《Java数据结构与算法》课程中的学习内容,重点介绍了非线性数据结构——树的相关知识及其应用。 ... [详细]
  • Nginx 反向代理与负载均衡实验
    本实验旨在通过配置 Nginx 实现反向代理和负载均衡,确保从北京本地代理服务器访问上海的 Web 服务器时,能够依次显示红、黄、绿三种颜色页面以验证负载均衡效果。 ... [详细]
  • 深入解析Java枚举及其高级特性
    本文详细介绍了Java枚举的概念、语法、使用规则和应用场景,并探讨了其在实际编程中的高级应用。所有相关内容已收录于GitHub仓库[JavaLearningmanual](https://github.com/Ziphtracks/JavaLearningmanual),欢迎Star并持续关注。 ... [详细]
  • 本文深入探讨了SQL数据库中常见的面试问题,包括如何获取自增字段的当前值、防止SQL注入的方法、游标的作用与使用、索引的形式及其优缺点,以及事务和存储过程的概念。通过详细的解答和示例,帮助读者更好地理解和应对这些技术问题。 ... [详细]
  • 本文介绍如何使用MFC和ADO技术调用SQL Server中的存储过程,以查询指定小区在特定时间段内的通话统计数据。通过用户界面选择小区ID、开始时间和结束时间,系统将计算并展示小时级的通话量、拥塞率及半速率通话比例。 ... [详细]
  • PHP 实现多级树形结构:构建无限层级分类系统
    在众多管理系统中,如菜单、分类和部门等模块,通常需要处理层级结构。为了高效管理和展示这些层级数据,本文将介绍如何使用 PHP 实现多级树形结构,并提供代码示例以帮助开发者轻松实现无限分级。 ... [详细]
  • Java多线程实现:从1到100分段求和并汇总结果
    本文介绍如何使用Java编写一个程序,通过10个线程分别计算不同区间的和,并最终汇总所有线程的结果。每个线程负责计算一段连续的整数之和,最后将所有线程的结果相加。 ... [详细]
  • 本文探讨了在 SQL Server 中使用 JDBC 插入数据时遇到的问题。通过详细分析代码和数据库配置,提供了解决方案并解释了潜在的原因。 ... [详细]
  • Java项目分层架构设计与实践
    本文探讨了Java项目中应用分层的最佳实践,不仅介绍了常见的三层架构(Controller、Service、DAO),还深入分析了各层的职责划分及优化建议。通过合理的分层设计,可以提高代码的可维护性、扩展性和团队协作效率。 ... [详细]
  • Python自动化测试入门:Selenium环境搭建
    本文详细介绍如何在Python环境中安装和配置Selenium,包括开发工具PyCharm的安装、Python环境的设置以及Selenium包的安装方法。此外,还提供了编写和运行第一个自动化测试脚本的步骤。 ... [详细]
  • 本文详细介绍了如何在云服务器上配置Nginx、Tomcat、JDK和MySQL。涵盖从下载、安装到配置的完整步骤,帮助读者快速搭建Java Web开发环境。 ... [详细]
author-avatar
castellaniygw_623
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有