欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

.net(C#数据库访问) Mysql,Sql server,Sqlite,Access四种数据库的连接方式

程序员文章站 2022-06-09 14:43:00
便签记录Mysql,Sql server,Sqlite,Access四种数据库的简单连接方式 //using MySql.Data.MySqlClient; #region 执行简单SQL语句,使用MySQL查询 static string strConn = "server=.;database= ......

便签记录mysql,sql server,sqlite,access四种数据库的简单连接方式

//using mysql.data.mysqlclient; 
#region  执行简单sql语句,使用mysql查询
        static string  strconn = "server=.;database=data20180608;uid=sa;pwd=123456;integrated security=sspi;persist security info=false;";
        /// <summary>
        /// 执行sql语句,返回影响的记录数
        /// </summary>
        /// <param name="sqlstring">sql语句</param>
        /// <returns>影响的记录数</returns>
        public static int myexecutesql(string sqlstring)
        {
            using (mysqlconnection connection = new mysqlconnection(strconn))
            {
                using (mysqlcommand cmd = new mysqlcommand(sqlstring, connection))
                {
                    try
                    {
                        connection.open();
                        int rows = cmd.executenonquery();
                        return rows;
                    }
                    catch (system.data.sqlclient.sqlexception e)
                    {
                        connection.close();
                        throw e;
                    }
                }
            }
        }
        /// <summary>
        /// 执行查询语句,返回datatable
        /// </summary>
        /// <param name="sqlstring">查询语句</param>
        /// <returns>dataset</returns>
        public static datatable myquery(string sqlstring)
        {
            using (mysqlconnection connection = new mysqlconnection(strconn))
            {
                dataset ds = new dataset();
                try
                {
                    connection.open();
                    mysqldataadapter command = new mysqldataadapter(sqlstring, connection);
                    command.fill(ds, "ds");
                }
                catch (system.data.sqlclient.sqlexception ex)
                {
                    throw new exception(ex.message);
                }
                return ds.tables[0];
            }
        }
        #endregion

 

 #region  执行简单sql语句,使用sql server查询
        static string strconn = "data source=.;database=data20180608;user id=root;password=123456;pooling=false;charset=utf8;port=3306;";

        /// <summary>
        /// 执行sql语句,返回影响的记录数
        /// </summary>
        /// <param name="sqlstring">sql语句</param>
        /// <returns>影响的记录数</returns>
        public static int executesql(string sqlstring)
        {
            using (sqlconnection connection = new sqlconnection(strconn))
            {
                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;
                    }
                }
            }
        }
        /// <summary>
        /// 执行查询语句,返回datatable
        /// </summary>
        /// <param name="sqlstring">查询语句</param>
        /// <returns>dataset</returns>
        public static datatable query(string sqlstring)
        {
            using (sqlconnection connection = new sqlconnection(strconn))
            {
                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.tables[0];
            }
        }
        #endregion

 

//using system.data.oledb;
 public static string otherpath = "";
        public static string strconn = "provider=microsoft.jet.oledb.4.0;data source=";
       public static string sql = string.empty;
        #region  执行简单sql语句,使用oledb查询

        /// <summary>
        /// 执行sql语句,返回影响的记录数
        /// </summary>
        /// <param name="sqlstring">sql语句</param>
        /// <returns>影响的记录数</returns>
        public static int executesql(string sqlstring)
        {
            using (oledbconnection connection = new oledbconnection(strconn + otherpath ))
            {
                using (oledbcommand cmd = new oledbcommand(sqlstring, connection))
                {
                    try
                    {
                        connection.open();
                        int rows = cmd.executenonquery();
                        return rows;
                    }
                    catch (system.data.sqlclient.sqlexception e)
                    {
                        connection.close();
                        throw e;
                    }
                }
            }
        }

      


        /// <summary>
        /// 执行查询语句,返回datatable
        /// </summary>
        /// <param name="sqlstring">查询语句</param>
        /// <returns>dataset</returns>
        public static datatable query(string sqlstring)
        {
            using (oledbconnection connection = new oledbconnection(strconn + otherpath))
            {
                dataset ds = new dataset();
                try
                {
                    connection.open();
                    oledbdataadapter command = new oledbdataadapter(sqlstring, connection);
                    command.fill(ds, "ds");
                }
                catch (exception ex)
                {
                    system.windows.forms.messagebox.show(ex.message);
                }
                return ds.tables[0];
            }
        }

        /// <summary>
        /// 执行查询语句,返回datatable
        /// </summary>
        /// <param name="sqlstring">查询语句</param>
        /// <returns>dataset</returns>
        public static datatable querydataname()
        {
            using (oledbconnection connection = new oledbconnection(strconn + otherpath))
            {
                datatable ds = new datatable();
                try
                {
                    connection.open();
                    ds = connection.getoledbschematable(oledbschemaguid.tables, new object[] { null, null, null, "table" });
                }
                catch (exception ex)
                {
                    system.windows.forms.messagebox.show(ex.message);
                }
                return ds;
            }
        }

        #endregion

 

 public static string strconn = @"data source=" + @"f:\资料文档\20190227\cad\0625anpin\caddllcl\data\cad_try0626.db";
        public static string sql = string.empty;

        /// <summary>
        /// 执行sql语句,返回影响的记录数
        /// </summary>
        /// <param name="sqlstring">sql语句</param>
        /// <returns>影响的记录数</returns>
        public static int executesql(string sqlstring)
        {
            using (sqliteconnection connection = new sqliteconnection(strconn))
            {
                //事务
                using (sqlitetransaction singletrans = connection.begintransaction(isolationlevel.readcommitted))
                {
                    using (sqlitecommand cmd = new sqlitecommand(sqlstring, connection))
                    {
                        try
                        {
                            connection.open();
                            cmd.transaction = singletrans;
                            int rows = cmd.executenonquery();
                            singletrans.commit();
                            return rows;
                        }
                        catch (system.data.sqlclient.sqlexception e)
                        {
                            connection.close();
                            singletrans.rollback();
                            throw e;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 执行查询语句,返回datatable
        /// </summary>
        /// <param name = "sqlstring" > 查询语句 </ param >
        /// < returns > dataset </ returns >
        public static datatable query(string sqlstring)
        {
            using (sqliteconnection connection = new sqliteconnection(strconn))
            {
                dataset ds = new dataset();
                try
                {
                    connection.open();
                    sqlitedataadapter command = new sqlitedataadapter(sqlstring, connection);
                    command.fill(ds, "ds");
                    return ds.tables[0];
                }
                catch (exception ex)
                {
                    connection.close();
                    throw ex;
                }
            }
        }


        /// <summary>
        /// 执行存储过程,返回影响的记录数
        /// </summary>
        /// <param name="sqlstring">sql语句</param>
        /// <returns>影响的记录数</returns>
        public static int executeproc(string procname, sqliteparameter[] coll)
        {
            using (sqliteconnection connection = new sqliteconnection(strconn))
            {
                //事务
                using (sqlitetransaction singletrans = connection.begintransaction(isolationlevel.readcommitted))
                {
                    using (sqlitecommand cmd = new sqlitecommand(connection))
                    {
                        try
                        {
                            connection.open();
                            for (int i = 0; i < coll.length; i++)
                            {
                                cmd.parameters.add(coll[i]);
                            }
                            cmd.commandtype = commandtype.storedprocedure;
                            cmd.commandtext = procname;
                            cmd.transaction = singletrans;
                            int rows = cmd.executenonquery();
                            singletrans.commit();
                            return rows;
                        }
                        catch (system.data.sqlclient.sqlexception e)
                        {
                            connection.close();
                            singletrans.rollback();
                            throw e;
                        }
                    }
                }
            }
        }


        /// <summary>
        /// 执行带参数的sql语句,返回影响的记录数
        /// </summary>
        /// <param name="sqlstring">sql语句</param>
        /// <returns>影响的记录数</returns>
        public static int executesqlpar(string sqlpar, sqliteparameter[] coll)
        {
            using (sqliteconnection connection = new sqliteconnection(strconn))
            {
                //事务
                using (sqlitetransaction singletrans = connection.begintransaction(isolationlevel.readcommitted))
                {
                    using (sqlitecommand cmd = new sqlitecommand(connection))
                    {
                        try
                        {
                            connection.open();
                            for (int i = 0; i < coll.length; i++)
                            {
                                cmd.parameters.add(coll[i]);
                            }
                            cmd.commandtype = commandtype.text;
                            cmd.commandtext = sqlpar;
                            cmd.transaction = singletrans;
                            int rows = cmd.executenonquery();
                            singletrans.commit();
                            return rows;
                        }
                        catch (system.data.sqlclient.sqlexception e)
                        {
                            connection.close();
                            singletrans.rollback();
                            throw e;
                        }
                    }
                }
            }
        }