.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; } } } } }
上一篇: 新生的婴儿打嗝是怎么回事啊
下一篇: C语言实现多线程定时器实例讲解