# Sanhao.DbService

**Repository Path**: themarcus/dbservice

## Basic Information

- **Project Name**: Sanhao.DbService
- **Description**: sqlhelper，提供sql快速连接和增删改查等操作
- **Primary Language**: C#
- **License**: MIT
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2023-10-07
- **Last Updated**: 2023-10-26

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# 简介

DbService提供DbHelper类，方便快速连接数据库进行增删改查。目前支持两种数据库（后面有时间再扩展其他数据库）

1. SqlServer
2. MySql

使用方法请参考下面的示例

1. 先建立连接，实例化DbHelper，传入数据库类型和连接字符串

   ```c#
   DbHelper dbHelper = new DbHelper(DbProvider.SqlServer, strConn);
   ```

   或者可以直接实例化子类，子类通过重写BulkInsert函数可以实现批量插入

   ```c#
   DbHelper dbHelper=new SqlHelper(strConn);
   //或者
   DbHelper dbHelper=new MySqlHelper(strConn);
   ```

2. 创建insert字符串，使用参数传入

   ```c#
   string strInsertSql = "insert into t_student (name,age,city,code) values(@name,@age,@city,@code)";
   ```

3. 创建参数列表，调用CreateDbParameter创建DbParameter子类实例（如SqlDbParameter，MySqlDbParameter。工厂方法，根据当前DbHelper实例中的数据库类型自动创建）

   ```c#
   pas.Add(dbHelper.CurrentDbFactory.CreateDbParameter("@name", name));
   ```

4. 调用ExecuteSql执行sql语句插入数据

   ```c#
   dbHelper.ExecuteSql(strInsertSql, pas.ToArray());
   ```

5. 调用GetTable查询数据

   ```c#
   DataTable result = dbHelper.GetTable(strQuerySql);
   ```

完整例子：

``` C#
public class Program
{
    public static void Main(string[] args)
    {
        //sqlserver
        //string strConn = "Server=YOURS;Database=YOURS;User ID=YOURS;Password=YOURS;Encrypt=True;TrustServerCertificate=True;";
        
        //mysql的连接字符串里要添加";AllowLoadLocalInfile=true"，即连接字符串的形式应该是"Server= ;Database=;User ID=;Password=;AllowLoadLocalInfile=true"，同时在mysql数据库上执行"set global local_infile=1"开启批量上传
        string strConn = "Server=YOURS; Port=3306;Stmt=; Database=YOURS; Uid=YOURS; Pwd=YOURS;AllowLoadLocalInfile=true;";
        DbHelper dbHelper = new MySqlHelper(strConn);
        BulkInsert(dbHelper);

    }

    static void Insert(DbHelper dbHelper)
    {
        string strInsertSql = "insert into t_student (name,age,city,code) values(@name,@age,@city,@code)";
        List<DbParameter> pas = new List<DbParameter>();
        Random rand = new Random();
        int tmp = rand.Next(1, 10);
        string name = "tom" + tmp;
        int age = tmp;
        string city = "DG" + tmp;
        string code = "1001" + "-" + tmp;
        pas.Add(dbHelper.CurrentDbFactory.CreateDbParameter("@name", name));
        pas.Add(dbHelper.CurrentDbFactory.CreateDbParameter("@age", age));
        pas.Add(dbHelper.CurrentDbFactory.CreateDbParameter("@city", city));
        pas.Add(dbHelper.CurrentDbFactory.CreateDbParameter("@code", code));
        dbHelper.ExecuteSql(strInsertSql, pas.ToArray());
    }

    static void BulkInsert(DbHelper dbHelper)
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("Name", typeof(string));
        dt.Columns.Add("Age", typeof(int));
        dt.Columns.Add("City", typeof(string));
        dt.Columns.Add("Code", typeof(string));
        for (int i = 0; i < 10; i++)
        {
            DataRow row = dt.NewRow();
            row["Name"] = "alex" + i;
            row["Age"] = i;
            row["City"] = "DG" + i;
            row["Code"] = "ST0" + i;
            dt.Rows.Add(row);
        }
        //批量插入需要传入DataTable
        dbHelper.BulkInsert(dt, "t_student");
    }

    static void Show(DbHelper dbHelper)
    {
        string strQuerySql = "select * from t_student";
        DataTable result = dbHelper.GetTable(strQuerySql);
        if (result.Rows.Count > 0)
        {
            foreach (DataRow row in result.Rows)
            {
                Console.WriteLine($"{row["name"]}");
            }
        }
    }
}

```
其他接口方法，请参考源码:https://gitee.com/null_966_0168/dbservice.git