本文主要提供SQL数据库操作方法,包括连接、读取、插入、更新、删除等操作,以及基本指令格式;仅供学习!
以下为程序源码:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb; // <- for database methods
using 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;
}
}