首页    技术博客    PHP教程    数据库技术    前端开发   HTML5    Nginx    php论坛
新用户注册 | 会员登录
加入会员，解锁专属内容
取消
热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

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

作者：castellaniygw_623 | 来源：互联网 | 2024-10-13 12:01
首先，需要连接SQLServer数据库的服务器名称server、数据库名database、数据库用户名uid以及密码pwd，如下图：然后需

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

 然后需要以下数据库SQL代码段&＃xff0c;还有一个myHelper.cs代码&＃xff0c;粘贴复制即可&＃xff1a;

DbHelperSQL.cs代码&＃xff1a;

注意要更改红框中的内容

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

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

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

myHelper.cs代码&＃xff1a;

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 &＃61; System.Environment.CurrentDirectory &＃43; "\\Resources\\Config.xml";public static void mySendMessageToMainWindow(string _str){if (showWarnMessage !&＃61; null){showWarnMessage(_str);}}public static string getXMLValue(string eleName){try{XmlDocument xmlDoc1 &＃61; new XmlDocument();xmlDoc1.Load(xmlPath);var root &＃61; xmlDoc1.DocumentElement;XmlNode nodes &＃61; xmlDoc1.SelectSingleNode("//" &＃43; eleName);if (nodes !&＃61; null){xmlDoc1.Save(xmlPath);return nodes.InnerText.ToString();}else{XmlElement xe1 &＃61; xmlDoc1.CreateElement(eleName);//创建一个节点 xe1.InnerText &＃61; "";root.AppendChild(xe1);xmlDoc1.Save(xmlPath);return "";}}catch (Exception tt){myHelper.errorLogAdd(tt.Message);return "";}}public static void errorLogAdd(string _str){try{_str &＃61; DateTime.Now.ToString("yyyyMMddhhmmss") &＃43; "-" &＃43; _str;if (System.IO.File.Exists("C://errorLog.txt")){StreamWriter sw &＃61; new StreamWriter("C://errorLog.txt", true, Encoding.GetEncoding("gb2312"));sw.WriteLine(_str);sw.Flush();sw.Close();}else{FileStream fs1 &＃61; new FileStream("C://errorLog.txt", FileMode.Create, FileAccess.Write);//创建
StreamWriter sw &＃61; new StreamWriter(fs1);sw.WriteLine(_str);sw.Flush();sw.Close();fs1.Close();}}catch { }}}
}

View Code

 

完成以上步骤之后&＃xff0c;在MainWindow.xaml中添加增、删、改三个按钮&＃xff1a;

<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



推荐阅读
  • select

    Oracle 11g 创建表空间与基础配置

    本文详细介绍了Oracle 11g中的创建表空间的方法，以及如何设置客户端和服务端的基本配置，包括用户管理、环境变量配置等。 ... [详细]
  • js

    SIP基础概览

    本文介绍了SIP（Session Initiation Protocol，会话发起协议）的基本概念、功能、消息格式及其实现机制。SIP是一种在IP网络上用于建立、管理和终止多媒体通信会话的应用层协议。 ... [详细]
  • js

    CentOS下ProFTPD的安装与配置指南

    CentOS下ProFTPD的安装与配置指南
    本文详细介绍在CentOS操作系统上安装和配置ProFTPD服务的方法，包括基本配置、安全设置及高级功能的启用。 ... [详细]
  • install

    Linux环境下MySQL的安装与部署指南

    本文详细介绍了在Linux操作系统上安装和部署MySQL数据库的过程，包括必要的环境准备、安装步骤、配置优化及安全设置等内容。 ... [详细]
  • instance

    解决Spring Cloud Eureka自定义端口时连接错误的问题

    在尝试通过自定义端口部署Spring Cloud Eureka时遇到了连接失败的问题。本文详细描述了问题的现象，并提供了有效的解决方案，以帮助遇到类似情况的开发者。 ... [详细]
  • instance

    Maven + Spring + MyBatis + MySQL 环境搭建与实例解析

    Maven + Spring + MyBatis + MySQL 环境搭建与实例解析
    本文详细介绍如何使用MySQL数据库进行环境搭建，包括创建数据库表并插入示例数据。随后，逐步指导如何配置Maven项目，整合Spring框架与MyBatis，实现高效的数据访问。 ... [详细]
  • sum

    UVALive 8201 - BBP 公式计算圆周率

    在1995年，Simon Plouffe 发现了一种特殊的求和方法来表示某些常数。两年后，Bailey 和 Borwein 在他们的论文中发表了这一发现，这种方法被命名为 Bailey-Borwein-Plouffe (BBP) 公式。该问题要求计算圆周率 π 的第 n 个十六进制数字。 ... [详细]
  • sum

    软件测试行业深度解析：迈向高薪的必经之路

    软件测试行业深度解析：迈向高薪的必经之路
    本文深入探讨了软件测试行业的发展现状及未来趋势，旨在帮助有志于在该领域取得高薪的技术人员明确职业方向和发展路径。 ... [详细]
  • js

    二维码的实现与应用

    二维码的实现与应用
    本文介绍了二维码的基本概念、分类及其优缺点，并详细描述了如何使用Java编程语言结合第三方库（如ZXing和qrcode.jar）来实现二维码的生成与解析。 ... [详细]
  • stream

    使用Service Locator模式实现高效的服务命名访问

    本文探讨了如何通过Service Locator模式来简化和优化在B/S架构中的服务命名访问，特别是对于需要频繁访问的服务，如JNDI和XMLNS。该模式通过缓存机制减少了重复查找的成本，并提供了对多种服务的统一访问接口。 ... [详细]
  • process

    MongoDB 高可用集群搭建指南：分片、读写分离与负载均衡

    本文详细介绍了如何搭建一个高可用的MongoDB集群，包括环境准备、用户配置、目录创建、MongoDB安装、配置文件设置、集群组件部署等步骤。特别关注分片、读写分离及负载均衡的实现。 ... [详细]
  • js

    深入理解：AJAX学习指南

    深入理解：AJAX学习指南
    本文详细探讨了AJAX的基本概念、工作原理及其在现代Web开发中的应用，旨在为初学者提供全面的学习资料。 ... [详细]
  • php

    ARM平台下构建SSH服务端并实现远程访问

    本文详细介绍了如何在ARM架构的目标设备上部署SSH服务端，包括必要的软件包下载、交叉编译过程以及最终的服务配置与测试。适合嵌入式开发人员和系统集成工程师参考。 ... [详细]
  • process

    Android SOAP 请求失败：服务器无法处理请求，参数值不能为空

    在尝试使用 Android 发送 SOAP 请求时遇到错误，服务器返回 '无法处理请求' 的信息，并指出某个值不能为 null。本文探讨了可能的原因及解决方案。 ... [详细]
  • select

    Oracle 数据库间通过 DBLink 进行交互的方法

    本文介绍了如何在两个Oracle数据库（假设为数据库A和数据库B）之间设置DBLink，以便能够从数据库A中直接访问和操作数据库B中的数据。文章详细描述了创建DBLink前的必要准备步骤以及具体的创建方法。 ... [详细]
author-avatar
castellaniygw_623
这个家伙很懒，什么也没留下！
Tags | 热门标签
RankList | 热门文章
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有