mssql不存在便插入存在不执行操作

创建表 CREATE TABLE Users ( UserId VARCHAR ( 50 ) PRIMARY KEY , UserName NVARCHAR ( 20 ), Age INT NOT NULL ) -- 直接插入 INSERT INTO Users VALUES ( ‘ 12138 ‘ , ‘ Wen ‘ , 2 ) -- 经sql判断的插入 INSERT INTO Users SELECT ‘ 12138 ‘ UserId , ‘ Wen ‘ UserName, 2 Age FROM ( SELECT ‘ 12138 ‘ UserId , ‘ Wen ‘ UserName, 2 Age) A LEFT JOIN Users B ON B.UserId = A.UserId WHERE B.UserId IS NULL -- 清空表数据 TRUNCATE TABLE Users

控制台程序测试

sql帮助类

  1   using   System;
   2   using   System.Data;
   3   using   System.Data.SqlClient;
   4  
  5   namespace   ConTest.Core
   6   {
   7       public   class   SqlDBHelper
   8       {
   9           ///   <summary> 
 10           ///   执行查询语句，返回DataSet
  11           ///   </summary> 
 12           ///   <param name="sSQLString">  查询语句  </param> 
 13           ///   <returns>  DataSet  </returns> 
 14           public   static  DataSet Query( string  sDBConnectionString,  string   sSQLString)
  15           {
  16               using  (SqlConnection connection =  new   SqlConnection(sDBConnectionString))
  17               {
  18                  DataSet ds =  new   DataSet();
  19                   try 
 20                   {
  21                       connection.Open();
  22                      SqlDataAdapter command =  new   SqlDataAdapter(sSQLString, connection);
  23                      command.Fill(ds,  "  ds  "  );
  24                   }
  25                   catch   (System.Data.SqlClient.SqlException ex)
  26                   {
  27                       throw   new   Exception(ex.Message);
  28                   }
  29                   return   ds;
  30               }
  31           }
  32  
 33           ///   <summary> 
 34           ///   执行SQL语句，返回影响的记录数
  35           ///   </summary> 
 36           ///   <param name="SQLString">  SQL语句  </param> 
 37           ///   <returns>  影响的记录数  </returns> 
 38           public   static   int  ExecuteSql( string  sDBConnectionString,  string   SQLString)
  39           {
  40               using  (SqlConnection connection =  new   SqlConnection(sDBConnectionString))
  41               {
  42                   using  (SqlCommand cmd =  new   SqlCommand(SQLString, connection))
  43                   {
  44                       try 
 45                       {
  46                           connection.Open();
  47                           int  rows =  cmd.ExecuteNonQuery();
  48                           return   rows;
  49                       }
  50                       catch   (System.Data.SqlClient.SqlException E)
  51                       {
  52                           connection.Close();
  53                           throw   new   Exception(E.Message);
  54                       }
  55                   }
  56               }
  57           }
  58       }
  59  }

View Code

控制台程序

  1   using   ConTest.Core;
   2   using   System;
   3   using   System.Text;
   4   using   System.Threading.Tasks;
   5  
  6   namespace   ConTest
   7   {
   8       class   Program
   9       {
  10           static   void  Main( string  [] args)
  11           {
  12              StringBuilder sbSql =  new   StringBuilder();
  13  
 14              Parallel.For( 0 ,  10 , item =>
 15                 {
  16                     try 
 17                     {
  18                         //  直接插入
  19                         //  var i = SqlDBHelper.ExecuteSql(ConstValues.SqlServerDBStr, $" INSERT INTO Users VALUES(‘12138‘,‘Wen‘,2); ");
  20  
 21                         //  经sql判断的插入 
 22                         var  i =  SqlDBHelper.ExecuteSql(ConstValues.SqlServerDBStr,
  23                         @"  
 24                       INSERT INTO Users
  25                       SELECT ‘12138‘ UserId ,‘Wen‘ UserName,2 Age
  26                       FROM(SELECT ‘12138‘ UserId ,‘Wen‘ UserName,2 Age) A
  27                       LEFT JOIN Users B
  28                       ON B.UserId=A.UserId
  29                       WHERE B.UserId IS NULL
  30                       "  );
  31  
 32                        sbSql.AppendLine($ "  True，Line：{i}  "  );
  33                     }
  34                     catch   (Exception ex)
  35                     {
  36                        sbSql.AppendLine($ "  False，{ex.Message}  "  );
  37                     }
  38                 });
  39  
 40               Console.WriteLine(sbSql.ToString());
  41  
 42               Console.Read();
  43           }
  44       }
  45  }

View Code

静态值

  1   namespace   ConTest.Core
   2   {
   3       ///   <summary> 
  4       ///   静态值
   5       ///   </summary> 
  6       public    class   ConstValues
   7       {
   8           ///   <summary> 
  9           ///   数据库连接字符串
  10           ///   </summary> 
 11           public   static   string  SqlServerDBStr =  "  data source=.;Initial Catalog=ConTest;integrated security=true;  "  ;
  12       }
  13  }

View Code

运行结果

直接插入

经sql判断

 

结语

实现标题描述

mssql不存在便插入存在不执行操作

标签：users   表数据   arc   ons   dbconnect   https   exception   core   threading   

查看更多关于mssql不存在便插入存在不执行操作的详细内容...