SQL数据库操作
来源：互联网 发布：双汇系统网址知多少 编辑：程序博客网 时间：2024/06/06 00:15
本文主要提供SQL数据库操作方法，包括连接、读取、插入、更新、删除等操作，以及基本指令格式；仅供学习！
以下为程序源码：
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Data.OleDb; // <- for database methodsusing System.Windows.Forms;using System.Data;using System.Data.SqlClient; //=================================================================== //==================// SQL数据库操作 //=================// //=================================================================== public class SqlDbObj { public SqlConnection m_DbConnect; public string strServerName = ""; public string strDBName = ""; public string strUserName = ""; public string strPassword = ""; public bool bUseWindowsLogin = true; public string GetSelectCmd(string TableName, bool bSelectAll = true, string KeyName = "", string strNum = "50", bool bIncrease = false) { string strCmd = ""; try { if (bSelectAll) strCmd = "SELECT * FROM " + TableName; else { if (bIncrease) strCmd = string.Format("SELECT TOP {0}* FROM {1}", strNum, TableName); else strCmd = string.Format("SELECT TOP {0}* FROM {1} order by {2} desc", strNum, TableName, KeyName); } } catch { } return strCmd; } // public string connectString = "Data Source=10.136.24.34\\MYSQL;initial Catalog=PCControl;User ID=sa;password=sa123456;"; // public static string connectString = "Server=10.136.24.34\\MYSQL;database=PCControl;Integrated Security = true;"; /// <summary> 连接成功，返回空字符，连接失败：返回错误信息 </summary> public string OpenDataBase(string ServerName, string DBName, string UserName = "", string Password = "", bool UseWindowsLogin = true) { try { string connectString = ""; if (UseWindowsLogin) { connectString = @"Server=" + ServerName + ";Integrated Security = true" + ";database=" + DBName + ";"; } else { connectString = @"Data Source=" + ServerName + ";initial Catalog=" + DBName + ";User ID=" + UserName + ";password=" + Password + ";"; } m_DbConnect = new SqlConnection(connectString); m_DbConnect.Open(); strServerName = ServerName; strDBName = DBName; strUserName = UserName; strPassword = Password; bUseWindowsLogin = UseWindowsLogin; return ""; } catch (Exception ex) { return "Error info:" + ex.Message; } } //关闭数据库 public void CloseDataBase() { try { m_DbConnect.Close(); } catch { } } public bool GetConnectState() { if (m_DbConnect == null || m_DbConnect.State == ConnectionState.Closed) return false; else return true; } void CheckConnect() { if (m_DbConnect == null || m_DbConnect.State == ConnectionState.Closed) { OpenDataBase(strServerName, strDBName, strUserName, strPassword, bUseWindowsLogin); } } /// <summary>插入指令（格式化插入） /// "INSERT INTO 表名(字段1，字段2，...,字段n) VALUES(内容1，内容2，...，内容n)" /// 注意 字符串要加单引号'' /// </summary> public string InsertCommand(string SQLInsertString) { try { if (!GetConnectState()) return "Not connected"; SqlCommand m_SqlCmd = new SqlCommand(); m_SqlCmd.Connection = m_DbConnect; m_SqlCmd.CommandType = CommandType.Text; m_SqlCmd.CommandText = SQLInsertString; int response = -1; response = m_SqlCmd.ExecuteNonQuery(); if (response >= 1) return ""; else return "Insert failed"; } catch (Exception ex) { return ex.Message; } } /// <summary>删除指令 /// "DELETE FROM 表名 where 字段 = " + "内容" + ""; /// 注意 字符串要加单引号'' /// </summary> public bool DeleteCommand(string SQLDeleteString) { try { if (!GetConnectState()) return false; SqlCommand m_SqlCmd = new SqlCommand(); m_SqlCmd.Connection = m_DbConnect; m_SqlCmd.CommandType = CommandType.Text; m_SqlCmd.CommandText = SQLDeleteString; m_SqlCmd.ExecuteNonQuery(); return true; } catch (Exception ex) { MessageBox.Show(ex.Message); return false; } } /// <summary> 更新指令 /// "UPDATE 表名 SET 字段1 =内容1, 字段2=内容2,...,字段n=内容n WHERE 查找字段=查找内容"; /// 注意 字符串要加单引号'' /// </summary> public bool UpdataCommand(string SQLUpdateString) { try { if (!GetConnectState()) return false; SqlCommand m_SqlCmd = new SqlCommand(); m_SqlCmd.Connection = m_DbConnect; m_SqlCmd.CommandType = CommandType.Text; m_SqlCmd.CommandText = SQLUpdateString; int response = m_SqlCmd.ExecuteNonQuery(); if (response >= 1) { MessageBox.Show("Update successful!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information); return true; } else { MessageBox.Show("更新失败，没有该项目"); return false; } } catch (Exception ex) { MessageBox.Show(ex.Message); return false; } } //"SELECT movieID, Title, Publisher, Previewed, MovieYear, Type FROM movie,movieType WHERE movietype.typeID = movie.typeID"; //"SELECT movieID, Title, Publisher, Previewed, MovieYear, Type FROM movie,movietype WHERE movietype.typeID = movie.typeID AND movie.title LIKE '" + title + "%'"; //"SELECT movieID, Title, Publisher, Previewed, MovieYear, Type FROM movie,movietype WHERE movietype.typeID = movie.typeID AND movie.typeID = " + type + ""; //"SELECT movieID, Title, Publisher, Previewed, MovieYear, Type FROM movie,movietype WHERE movietype.typeID = movie.typeID AND movie.MovieYear BETWEEN " + yr1 + " AND " + yr2 + ""; //"SELECT movieID, Title, Publisher, Previewed, MovieYear, Type FROM movie,movietype WHERE movietype.typeID = movie.typeID AND Previewed ='" + previewed + "'"; /// <summary> 选择指令 /// "SELECT 字段1, 字段2,...,字段n FROM 表名1,表名2 WHERE 查找字段=查找内容"; /// 注意 字符串要加单引号'' /// </summary> public int SelectCommand(string SQLSelectString) { if (!GetConnectState()) return 0; SqlCommand m_SqlCmd = new SqlCommand(); m_SqlCmd.Connection = m_DbConnect; m_SqlCmd.CommandType = CommandType.Text; m_SqlCmd.CommandText = SQLSelectString; SqlDataReader sqlReader = m_SqlCmd.ExecuteReader(); while (sqlReader.Read()) { //if (textBox_find.Text == sqlReader["Map"].ToString()) //{ // listBox1.Items.Add(Convert.ToString(sqlReader["Map"] + " -> " + sqlReader["Program"])); //} } int SS = sqlReader.VisibleFieldCount; sqlReader.Close(); DataSet dataSet = new DataSet(); SqlDataAdapter dataAdapter = new SqlDataAdapter(m_SqlCmd); dataAdapter.Fill(dataSet); return SS; } /// <summary> 选择指令,返回DataSet /// "SELECT 字段1, 字段2,...,字段n FROM 表名1,表名2 WHERE 查找字段=查找内容"; /// 注意 字符串要加单引号'' /// </summary> public DataSet SelectCommand_DS(string SQLSelectString, string strTableName = "DataSet1") { DataSet dataSet = new DataSet(); try { if (!GetConnectState()) return null; SqlCommand m_SqlCmd = new SqlCommand(); m_SqlCmd.Connection = m_DbConnect; m_SqlCmd.CommandType = CommandType.Text; m_SqlCmd.CommandText = SQLSelectString; if (m_SqlCmd.Connection == null) return null; SqlDataAdapter dataAdapter = new SqlDataAdapter(m_SqlCmd); dataAdapter.Fill(dataSet, strTableName); } catch (Exception ex) { MessageBox.Show(ex.Message); } return dataSet; } /// <summary> 选择指令,返回DataSet /// "SELECT 字段1, 字段2,...,字段n FROM 表名1,表名2 WHERE 查找字段=查找内容"; /// 注意 字符串要加单引号'' /// </summary> public DataTable SelectCommand_DT(string SQLSelectString, string strTableName = "DataTable1") { DataTable dataTable = new DataTable(); try { if (!GetConnectState()) return null; SqlCommand m_SqlCmd = new SqlCommand(); m_SqlCmd.Connection = m_DbConnect; m_SqlCmd.CommandType = CommandType.Text; m_SqlCmd.CommandText = SQLSelectString; if (m_SqlCmd.Connection == null) return null; SqlDataAdapter dataAdapter = new SqlDataAdapter(m_SqlCmd); dataAdapter.Fill(dataTable); } catch (Exception ex) { MessageBox.Show(ex.Message); } return dataTable; } }阅读全文
0 0
- 数据库 SQL数据库操作
- 数据库(sql)操作
- Access数据库SQL操作
- Access数据库SQL操作
- SQL数据库操作类
- VFP操作SQL数据库
- SQL-创建数据库操作
- SQl 数据库相关操作
- SQL语句操作数据库
- sql数据库批量操作
- SQL数据库常用操作
- SQL Server 数据库操作
- sql语句操作数据库
- sql 跨数据库操作
- SQL数据库一些操作
- SQL 数据库操作
- js 操作sql数据库
- SQL数据库基本操作
- Python一些小知识
- Swift 4 JSON 解析指南
- Leetcode Word Break II
- 虚拟机中vsftpd上传速度慢的解决办法
- linux ubuntu修改用户密码
- SQL数据库操作
- 欧拉线性筛法求素数(顺便实现欧拉函数的求值)
- Mac OS X ifconfig命令解释
- Android集合数据对比是否相同
- HTTP Error 500.19
- JAVA：MD5-MD2计算
- spring 返回json数据
- UrlConnection
- 使用GoEasy实现web实时推送
