sqlline.cs

用于C#编程用的数据库连接

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Text;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Net;
using System.Net.Sockets;
/////ZHX再版v1.2v3 开源才是王道
namespace SQL链接
{ 
    public class SQL链接
    {
        #region  准备及链接
        private OleDbDataAdapter adadapter;
        private OleDbConnection sqlconn;
        private char aa = '\"';
        public string strSqlconn;
        /// <summary>
        /// 链接数据库
        /// </summary>
        /// <param name="hostname">服务器名称</param>
	SQL链接(string hostname) //重载一
        {
            try
            {
                strSqlconn = @"data source=" + hostname + ";initial catalog=Master;integrated security=SSPI;Provider=SQLOLEDB.1";
                sqlconn = new OleDbConnection(strSqlconn);
                sqlconn.Open();
            }
            catch
            {

            }
        }
        public SQL链接() //重载二
        {
            try
            {
                strSqlconn = @"data source=" + Dns.GetHostName() + ";initial catalog=Master;integrated security=SSPI;Provider=SQLOLEDB.1";
                sqlconn = new OleDbConnection(strSqlconn);
                sqlconn.Open();
            }
            catch
            {
            }
        }
        #endregion       
        #region 添加
        /// <summary>
        /// ZHX’"添加"方法
        /// </summary>
        /// <param name="dbneme">数据库名称</param>
        /// <param name="tbneme">表名称</param>
        /// <param name="coname">列名(数组)</param>
        /// <param name="f1">添加的值(数组)</param>
        /// <returns></returns>
        public DataSet 添加(string dbneme, string tbneme, string[] coname, string[] f1) 
        {
            DataSet dbs = new DataSet();
            string tp = null, tp1 = null;
            for (int i = 0; i < f1.Length; i++)
            {
                    tp += aa + coname[i] + aa + ",";
            }
            tp = tp.Remove(tp.Length - 1);
            for (int i = 0; i < f1.Length; i++)
            {
                    tp1 += "'" + f1[i] + "'" + ",";
            }
            tp1 = tp1.Remove(tp1.Length - 1);
            string cmdstring = "insert into " + aa + dbneme + aa + ".." + aa + tbneme + aa + " (" + tp + ")" + " values" + " (" + tp1 + ")";
            adadapter = new OleDbDataAdapter(cmdstring, sqlconn);
            adadapter.Fill(dbs);
            return dbs;
            sqlconn.Close();
        }
 /// <summary>
        /// ZHX’"添加"方法
        /// </summary>
        /// <param name="dbneme">数据库名称</param>
        /// <param name="tbneme">表名称</param>
        /// <param name="coname">列名</param>
        /// <param name="f1">添加的值</param>
        /// <returns></returns>
        public DataSet 添加(string dbneme, string tbneme, string coname, string f1) 
        {
            DataSet dbs = new DataSet();
            string tp = null, tp1 = null;
            tp += aa + coname + aa;
             tp1 += "'" + f1 + "'";
            string cmdstring = "insert into " + aa + dbneme + aa + ".." + aa + tbneme + aa + " (" + tp + ")" + " values" + " (" + tp1 + ")";
            adadapter = new OleDbDataAdapter(cmdstring, sqlconn);
            adadapter.Fill(dbs);
            return dbs;
            sqlconn.Close();
        }
        #endregion
        #region 删除
        /// <summary>
        /// ZHX’"删除"方法
        /// </summary>
        /// <param name="dbneme">数据库名称</param>
        /// <param name="tbneme">表名称</param>
        /// <param name="coname">列名(数组)</param>
        /// <param name="f1">删除的值(数组)</param>
        /// <returns></returns>
        public DataSet 删除(string dbneme, string tbneme, string[] coname, string[] f1)
        {
            DataSet dbs = new DataSet();
            string tp = null;
            for (int i = 0; i < f1.Length; i++)
            {
                tp += aa + coname[i] + aa + "=" + "'" + f1[i] + "'";
                tp += " and ";
            }
            tp = tp.Remove(tp.Length - 5);
            string cmdstring = "delete from " + aa + dbneme + aa + ".." + aa +tbneme + aa + " where " + tp;
            adadapter = new OleDbDataAdapter(cmdstring, sqlconn);
            adadapter.Fill(dbs);
            return dbs;
            sqlconn.Close();
        }
        /// <summary>
        /// ZHX’"删除"方法
        /// </summary>
        /// <param name="dbneme">数据库名称</param>
        /// <param name="tbneme">表名称</param>
        /// <param name="coname">列名</param>
        /// <param name="f1">删除的值</param>
        /// <returns></returns>
        public DataSet 删除(string dbneme, string tbneme, string coname, string f1)
        {
            DataSet dbs = new DataSet();
            string tp = null;
            tp += aa + coname + aa + "=" + "'" + f1 + "'";
            string cmdstring = "delete from " + aa + dbneme + aa + ".." + aa + tbneme + aa + " where " + tp;
            adadapter = new OleDbDataAdapter(cmdstring, sqlconn);
            adadapter.Fill(dbs);
            return dbs;
            sqlconn.Close();
        }
        #endregion
        #region 修改 //也许会有BUG
        /// <summary>
        /// ZHX’"修改"方法
        /// </summary>
        /// <param name="dbneme">数据库名称</param>
        /// <param name="tbneme">表名称</param>
        /// <param name="coname">列值(数组)</param>
        /// <param name="f1">列名(数组)</param>
        /// <param name="f2">设置的值(数组)</param>
        /// <returns></returns>
        public DataSet 修改(string dbneme, string tbneme, string[] coname, string[] f1,string[] f2)
        {
            DataSet dbs = new DataSet();
            string tp = null, tp1 = null;
            for (int i = 0; i < f1.Length; i++)
            {
                tp += aa + f1[i] + aa + "='" + f2[i] + "' ,";
            }
            tp = tp.Remove(tp.Length - 1);
            for (int i = 0; i < f1.Length; i++)
            {
                tp1 += aa + f1[i] + aa + "='" + coname[i] + "'" + " and";
            }
            tp = tp.Remove(tp.Length - 1);
            tp1 = tp1.Remove(tp1.Length - 4);
            string cmdstring = "UPDATE " + aa + dbneme + aa + ".." + aa + tbneme + aa + " SET " + tp + " WHERE " + tp1;
            adadapter = new OleDbDataAdapter(cmdstring, sqlconn);
            adadapter.Fill(dbs);
            return dbs;
            sqlconn.Close(); 
        }
        /// <summary>
        /// ZHX’"修改"方法
        /// </summary>
        /// <param name="dbneme">数据库名称</param>
        /// <param name="tbneme">表名称</param>
        /// <param name="coname">列值</param>
        /// <param name="f1">列名</param>
        /// <param name="f2">设置的值</param>
        /// <returns></returns>
        public DataSet 修改(string dbneme, string tbneme, string coname, string f1,string f2)
        {
            DataSet dbs = new DataSet();
            string tp = null, tp1 = null;
            tp += aa + coname + aa + "=" + f2;
            tp1 += aa + coname + aa + "='" + f1 + "'";
            string cmdstring = "UPDATE " + aa + dbneme + aa + ".." + aa + tbneme + aa + " SET " + tp + " WHERE " + tp1;
            adadapter = new OleDbDataAdapter(cmdstring, sqlconn);
            adadapter.Fill(dbs);
            return dbs;
            sqlconn.Close();
        }
        #endregion
        #region 查找
        /// <summary>
        /// ZHX’"查找"方法
        /// </summary>
        /// <param name="dbneme">数据库名称</param>
        /// <param name="tbneme">表名称</param>
        /// <param name="coname">列名(数组)</param>
        /// <param name="f1">查找的值(数组)</param>
        /// <returns></returns>
        public DataSet 查找(string dbneme, string tbneme, string[] coname, string[] f1)
        {
            DataSet dbs = new DataSet();
            string tp = null;
            for (int i = 0; i < f1.Length; i++)
            {
                tp += aa + coname[i] + aa + "=" + "'" + f1[i] + "'";
                tp += " and ";
            }
            tp = tp.Remove(tp.Length - 5);
            string cmdstring = "select * from " + aa + dbneme + aa + ".." + aa + tbneme + aa + " where " + tp;
            adadapter = new OleDbDataAdapter(cmdstring, sqlconn);
            adadapter.Fill(dbs);
            return dbs;
            sqlconn.Close();
        }
        /// <summary>
        /// ZHX’"查找"方法
        /// </summary>
        /// <param name="dbneme">数据库名称</param>
        /// <param name="tbneme">表名称</param>
        /// <param name="coname">列名</param>
        /// <param name="f1">查找的值</param>
        /// <returns></returns>
        public DataSet 查找(string dbneme, string tbneme, string coname, string f1)
        {
            DataSet dbs = new DataSet();
            string tp = null;
            tp += aa + coname + aa + "=" + "'" + f1 + "'";
            string cmdstring = "select * from " + aa + dbneme + aa + ".." + aa + tbneme + aa + " where " + tp;
            adadapter = new OleDbDataAdapter(cmdstring, sqlconn);
            adadapter.Fill(dbs);
            return dbs;
            sqlconn.Close();
        }
        #endregion
        #region 浏览表
        /// <summary>
        /// ZHX’"浏览表"方法
        /// </summary>
        /// <param name="tbneme">表名</param>
        /// <param name="dbneme">数据库名称</param>
        /// <param name="dbs">数据填充对象</param>
        /// <returns></returns>
        public DataSet 浏览表(string dnname,string tbneme)
        {
            DataSet dbs = new DataSet();
            string cmdstring ="use "+dnname+" select * from " + tbneme ;
            adadapter = new OleDbDataAdapter(cmdstring, sqlconn);
            adadapter.Fill(dbs);
            return dbs;
            sqlconn.Close();
        }
        #endregion
        #region 关闭连接
        public void 关闭连接() 
        {
            sqlconn.Close();
        }
        #endregion
    }
}