using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.SqlClient;namespace DBComm{ static class DBCommand { public class DBParameters { private SqlCommand m_owner = null; publi
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.SqlClient; namespace DBComm { static class DBCommand { public class DBParameters { private SqlCommand m_owner = null; public DBParameters(SqlCommand owner) { m_owner = owner; } public SqlParameterCollection P() { return m_owner.Parameters; } }; public static bool BulkToDB(string tabname, DataTable dt, params string[] destColumnNames) { bool bRet = false; do { if (dt == null) break; if (dt.Rows.Count == 0) break; using (SqlConnection conn = DBConn.GetConn()) { if (conn == null) break; SqlBulkCopy bulkcopy = new SqlBulkCopy(conn); if (bulkcopy == null) break; bulkcopy.DestinationTableName = tabname; bulkcopy.BulkCopyTimeout = 30; if (destColumnNames.Length == 0) { foreach (DataColumn col in dt.Columns) bulkcopy.ColumnMappings.Add(col.ColumnName, col.ColumnName); } else { if (destColumnNames.Length == dt.Columns.Count) { for (int i = 0; i < destColumnNames.Length; ++i) { bulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, destColumnNames[i]); } } } bulkcopy.BatchSize = dt.Rows.Count; try { bulkcopy.WriteToServer(dt); } catch (System.Exception e) { string err = e.Message; break; } finally { bulkcopy.Close(); } } bRet = true; } while (false); return bRet; } public static DBParameters ExecProcNonQuery(string proc_name, object[] paraValues) { using (SqlConnection conn = DBConn.GetConn()) { SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = proc_name; AddInParaValues(cmd, paraValues); cmd.ExecuteNonQuery(); return new DBParameters(cmd); } } public delegate T[] FillValues<T>(SqlDataReader reader); public static T[] QuerySomes<T>(string sql, FillValues<T> fill) { using (SqlConnection conn = DBConn.GetConn()) { T[] result = null; SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = sql; SqlDataReader reader = null; lock (reader = cmd.ExecuteReader()) { try { result = fill(reader); } catch (Exception e) { throw new Exception(e.StackTrace); } finally { reader.Close(); } } return result; } } public delegate object FillValue(SqlDataReader reader); public static object QuerySome(string sql, FillValue fill) { using (SqlConnection conn = DBConn.GetConn()) { object result = null; SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = sql; SqlDataReader reader = null; lock (reader = cmd.ExecuteReader()) { try { result = fill(reader); } catch (Exception e) { throw new Exception(e.StackTrace); } finally { reader.Close(); } } return result; } } public static object FillResultValue(SqlDataReader reader) { object o = null; if (reader.Read()) { o = reader.GetValue(0); } return o; } public static bool QueryBoolean(string sql) { return Convert.ToBoolean(QuerySome(sql, new FillValue(FillResultValue))); } public static byte[] QueryBytes(string sql) { return (byte[])(QuerySome(sql, new FillValue(FillResultValue))); } public static int QueryInteger(string sql) { return Convert.ToInt32(QuerySome(sql, new FillValue(FillResultValue))); } public static string QueryStr(string sql) { return QuerySome(sql, new FillValue(FillResultValue)) as string; } private static string[] FillStrsValue(SqlDataReader reader) { List<string> lststr = new List<string>(); while (reader.Read()) { lststr.Add(reader.GetString(0)); } return lststr.ToArray(); } public static string[] QueryStrs(string sql) { return QuerySomes(sql, new FillValues<string>(FillStrsValue)); } private static bool[] FillBooleansValue(SqlDataReader reader) { List<bool> lstbool = new List<bool>(); while (reader.Read()) { lstbool.Add(reader.GetBoolean(0)); } return lstbool.ToArray(); } public static bool[] QueryBooleans(string sql) { return QuerySomes(sql, new FillValues<bool>(FillBooleansValue)); } public static void ExecCmd(string sql) { using (SqlConnection conn = DBConn.GetConn()) { SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = sql; cmd.ExecuteNonQuery(); } } /// <summary> /// 获取存储过程的参数列表 /// </summary> /// <param name="proc_Name">存储过程名称</param> /// <returns>DataTable</returns> private static DataTable GetParameters(SqlConnection conn, string proc_Name) { SqlCommand comm = new SqlCommand("dbo.sp_sproc_columns", conn); comm.CommandType = CommandType.StoredProcedure; comm.Parameters.AddWithValue("@procedure_name", (object)proc_Name); SqlDataAdapter sda = new SqlDataAdapter(comm); DataTable dt = new DataTable(); sda.Fill(dt); return dt; } /// <summary> /// 为 SqlCommand 添加参数及赋值 /// </summary> /// <param name="comm">SqlCommand</param> /// <param name="paraValues">参数数组(必须遵循存储过程参数列表的顺序)</param> private static void AddInParaValues(SqlCommand comm, params object[] paraValues) { using (SqlConnection conn = DBConn.GetConn()) { comm.Parameters.Add(new SqlParameter("@RETURN_VALUE", SqlDbType.Int)); comm.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue; if (paraValues != null) { DataTable dt = GetParameters(conn, comm.CommandText); int i = 0; foreach (DataRow row in dt.Rows) { string key = row[3].ToString(); if (key != "@RETURN_VALUE") { int value = int.Parse(row[4].ToString()); if (value == 1) { comm.Parameters.AddWithValue(key, paraValues[i]); } else if (value == 2)//value为2则是输出参数 { comm.Parameters.AddWithValue(key, paraValues[i]).Direction = ParameterDirection.Output; //comm.Parameters[key].Direction = ParameterDirection.Output; } comm.Parameters[key].Size = Convert.ToInt32(row[7].ToString()); i++; } } } } } } }
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; namespace DBComm { class DBConn { private static string m_connstr; public static string ConnString { get { return m_connstr; } private set { m_connstr = value; } } static DBConn() { SqlConnectionStringBuilder connStr = new SqlConnectionStringBuilder(); connStr.DataSource = "."; connStr.InitialCatalog = "test"; connStr.IntegratedSecurity = true; connStr.Pooling = true; //开启连接池 connStr.MinPoolSize = 0; //设置最小连接数为0 connStr.MaxPoolSize = 100; //设置最大连接数为100 connStr.ConnectTimeout = 10; //设置超时时间为10秒 ConnString = connStr.ConnectionString; //ConnectDB(ConnString); } public static SqlConnection GetConn() { SqlConnection conn = new SqlConnection(ConnString); conn.Open(); return conn; } } }
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Data; namespace DBComm { static class DBTableSource { public static DataTable GetSource(SqlConnection conn, string strsql) { DataTable dt = null; SqlCommand cmd = null; SqlDataAdapter ad = null; try { lock (dt = new DataTable()) { if (conn is SqlConnection) { cmd = new SqlCommand(strsql, conn); ad = new SqlDataAdapter((SqlCommand)cmd); } dt.Clear(); ad.Fill(dt); } } catch (Exception e) { throw e; } return dt; } public static DataTable Source(string strsql) { using (SqlConnection conn = DBConn.GetConn()) { return GetSource(conn, strsql); } } } }

In MySQL, sorting uses the ORDERBY clause, and ranking uses the RANK(), DENSE_RANK(), and ROW_NUMBER() functions. 1. Sort: Use ORDERBY clause, such as SELECT*FROMemployeesORDERBYsalaryDESC; 2. Ranking: Use window functions, such as SELECTemployee_name, salary, RANK()OVER(ORDERBYsalaryDESC)ASrankFROMemployees; these operations are based on SQL query optimizer and execution engine, and are often used to sort quickly or merge sort, and ranking depends on window function calculation.

To create and call stored procedures in MySQL, follow the following steps: 1. Create stored procedures: Use the CREATEPROCEDURE statement to define stored procedures, including names, parameters, and SQL statements. 2. Compile stored procedures: MySQL compiles stored procedures into executable code and stores them. 3. Call stored procedure: use CALL statement and pass parameters. 4. Execute stored procedures: MySQL executes the SQL statements in it, processes parameters and returns the result.

The MySQL service can be set to automatically start on Windows, Linux, and macOS. 1) On Windows, use the command "scconfigmysqlstart=auto" to configure. 2) On Linux, enable it using "sudosystemctlenablemysql". 3) On macOS, create and load the launchd configuration file to achieve automatic startup.

The methods to view the MySQL table structure include: 1. Use the DESCRIBE command to view column information; 2. Use the SHOWCREATETABLE command to view table creation statements; 3. Use information_schema to query more detailed information. These methods help to quickly understand table structure and improve work efficiency.

Installing MySQL on macOS can be achieved through the following steps: 1. Install Homebrew, using the command /bin/bash-c"$(curl-fsSLhttps://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)". 2. Update Homebrew and use brewupdate. 3. Install MySQL and use brewinstallmysql. 4. Start MySQL service and use brewservicesstartmysql. After installation, you can use mysql-u

In MySQL, conditional filtering is implemented through the WHERE clause and grouping is completed through the GROUPBY clause. 1. Use the WHERE clause to filter data, such as finding employees with salary above 5,000. 2. Use the GROUPBY clause to group and aggregate data, such as counting the number of employees by department. 3. Choose the appropriate index to optimize query performance and avoid using functions or expressions as WHERE conditions. 4. Combining subqueries and EXPLAIN commands improve the efficiency of complex queries.

In MySQL, clearing table data but preserving table structure can be implemented through the TRUNCATETABLE and DELETE commands. 1. The TRUNCATETABLE command quickly deletes all records and resets the self-increment column. 2. The DELETE command deletes data line by line, does not reset the self-increment column, and can delete specific records in combination with the WHERE clause.

Deduplication in MySQL mainly uses DISTINCT and GROUPBY. 1.DISTINCT is used to return unique values, such as SELECTDISTINCTname, ageFROMusers. 2. GROUPBY realizes deduplication through grouping and can perform aggregation operations, such as SELECTid, name, MAX(created_at)aslatest_dateFROMusersGROUPBYname.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Atom editor mac version download
The most popular open source editor

DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

Zend Studio 13.0.1
Powerful PHP integrated development environment
