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

便签记录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;
                        }
                    }
                }
            }
        }