復(fù)制一個(gè)表的數(shù)據(jù)到另一個(gè)表

當(dāng)這兩個(gè)表的結(jié)構(gòu)相同且屬于不同的數(shù)據(jù)庫(kù)時(shí)，可采用插入的方法：
以兩個(gè)庫(kù)的location_map表為例：

INSERT INTO [GHNEWTEST.DB].[dbo].[R_STORAGE_LOCATION_MAP_T] SELECT 
[RACK_NO],
[PALLET_ID],
[STATE],
[FREEZE],
[CREATE_TIME] 
FROM [GZHTWarehouse.DB].[dbo].[R_STORAGE_LOCATION_MAP_T]

注意：選擇原數(shù)據(jù)的表字段時(shí)要剔除掉 ID，因?yàn)?ID 無(wú)法復(fù)制，一般都是自增長(zhǎng)的

為某個(gè)字段已存在的數(shù)據(jù)中添加內(nèi)容

UPDATE C_STORAGE_LOCATION_BASE_T SET RACK_NO = 'A' + RACK_NO

此語(yǔ)句功能為RACK_NO的每個(gè)數(shù)據(jù)前加上字符 A
效果圖

SUBSTRING用法

substring('abdcsef',1,3)
Sql Server中括號(hào)中數(shù)字‘1’表示截取的起始位置是從該字符串第一個(gè)字符開(kāi)始，‘3’表示截取后得到的字符串長(zhǎng)度為3個(gè)字符。
結(jié)果：
abd

修改某個(gè)字段已存在的數(shù)據(jù)的內(nèi)容

UPDATE C_STORAGE_LOCATION_BASE_T SET RACK_NO = REPLACE(RACK_NO, 'A', 'B') 

此語(yǔ)句功能為將RACK_NO的每個(gè)數(shù)據(jù)中的 A 替換成 B
效果圖

將一個(gè)表的數(shù)據(jù)插入到另一個(gè)表中

  INSERT INTO R_STORAGE_LOCATION_MAP_T (RACK_NO) 
  SELECT RACK_NO FROM C_STORAGE_LOCATION_BASE_T where ID>754

此語(yǔ)句功能為將base表中ID>754的RACK_NO數(shù)據(jù)插入到MAP表中

模糊查詢

if (!string.IsNullOrWhiteSpace(partName))
 {
   sql.Append(" AND PART_NAME LIKE @PARTNAME ");
   dbParams.Add("@PARTNAME", SqlDbType.NVarChar,"%"+partName+"%");
 }

App.config相關(guān)說(shuō)明

<add key="GH.DB" value="Data Source=.,1433;Network Library=DBMSSOCN;Initial Catalog=GZHTWarehouse.DB;User ID=sa;Password=12345;"/>

• Data Source=.中的 . 代表localhost
• 應(yīng)用程序入口的main中的SqlServerString.SqlConnectString = "GH.DB";要和App.config中的key="GH.DB"一致

顯示指定數(shù)據(jù)行數(shù)

加top("行數(shù)")即可，例：

SELECT top(20) * FROM C_KEYPART_BASE_T

即只顯示KEYPART_BASE表的前20行數(shù)據(jù)

參數(shù)為list<string>查詢

public ExecutionResult SearchByBoxNums(List<string> boxNums)
{
  string sql = null;
  ExecutionResult exeResult = null;
  string boxNo = "";
  foreach (var item in boxNums)
  {
     boxNo += "'" + item + "',";
  }
  boxNo = boxNo.Substring(0, boxNo.Length - 1);
  sql = string.Format(@"SELECT * FROM R_BOX_MAP_T where BOX_NO in ({0})", boxNo);
  DBParameter dbParams = dbParams = new DBParameter();
  exeResult = sqlHelper.ExecuteQueryDS(sql.ToString(), dbParams.GetParameters());
  return exeResult;
}

若參數(shù)為List<int>類型，則把foreach內(nèi)改為boxNo += item + ",";

在DAO層做事務(wù)處理

將要處理的多張表單獨(dú)寫多個(gè)sql，然后進(jìn)行統(tǒng)一拼接執(zhí)行處理

public ExecutionResult UpdateQty(string ApplyNo, string PalletNo, string BoxNo, string KPN, string Batch, string count, string taskNo, string user)
        {
            ExecutionResult exeResult;
            exeResult = new ExecutionResult();
            DBParameter dbParams;
            dbParams = new DBParameter();
            StringBuilder sb;
            sb = new StringBuilder();
            SQLTransactionHelper trans;
            trans = new SQLTransactionHelper();

            sb.Append(" UPDATE R_MATERIAL_SHEET_T SET ISSUED_QTY=ISSUED_QTY+@QTY WHERE APPLY_NO=@APPLY_NO AND PART_NO=@KPN; ");

            sb.Append(@" INSERT INTO R_STORAGE_KEYPARTS_RECORD_T
           (OPERATION_ID, OPERATION_TYPE, PALLET_NO, BOX_NO , PART_NO , PART_BATCH_NUMBER,UNIT_QTY,CREATE_USER,CREATE_TIME)
           VALUES(@TASK_NO,'出庫(kù)',@PALLET,@BOX,@KPN,@BATCH,@QTY,@USER,GETDATE()) ");

            dbParams.Add("@APPLY_NO", SqlDbType.NVarChar, ApplyNo);
            dbParams.Add("@TASK_NO", SqlDbType.NVarChar, taskNo);
            dbParams.Add("@PALLET", SqlDbType.NVarChar, PalletNo);
            dbParams.Add("@BOX", SqlDbType.NVarChar, BoxNo);
            dbParams.Add("@KPN", SqlDbType.NVarChar, KPN);
            dbParams.Add("@BATCH", SqlDbType.NVarChar, Batch);
            dbParams.Add("@QTY", SqlDbType.Int, int.Parse(count ?? "0"));
            dbParams.Add("@USER", SqlDbType.NVarChar, user);

            try
            {
                trans.BeginTransaction();
                trans.ExecuteUpdate(sb.ToString(), dbParams.GetParameters());
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                trans.EndTransaction();
            }
            return exeResult;
        }

用sql生成倉(cāng)位基礎(chǔ)數(shù)據(jù)

以生成貴航倉(cāng)庫(kù)2排×13列×29層儲(chǔ)位為例

DECLARE @i int, @j int, @k int, @str varchar,@rackNo varchar(50) --i列號(hào)，j層號(hào)，k排號(hào)
 set @i=0
 set @j=0
 set @k=0
 set @rackNo=''
 WHILE @k<2
 begin
 set @i=0
 if @k=0
  begin set @str='A' end
 if @k=1
  begin set @str='B' end
 
WHILE @i<13
BEGIN
set @j=0
 WHILE @j < 29
 begin
 if (@i < 9)
 begin
  if(@j < 9) 
   begin set @rackNo = '0'+convert(varchar(50),(@k+1))+'0'+convert(varchar(50),(@i+1)) + '0' + convert(varchar(50),(@j+1)) end
  else
   begin set @rackNo = '0'+convert(varchar(50),(@k+1))+'0'+convert(varchar(50),(@i+1)) + convert(varchar(50),(@j+1)) end
 end
 
 else
 begin
   if(@j < 9) 
    begin set @rackNo = '0'+convert(varchar(50),(@k+1))+convert(varchar(50),(@i+1)) + '0' + convert(varchar(50),(@j+1)) end
  else
    begin set @rackNo = '0'+convert(varchar(50),(@k+1))+convert(varchar(50),(@i+1)) + convert(varchar(50),(@j+1)) end
 end
    BEGIN
    INSERT INTO C_STORAGE_LOCATION_BASE_T (STORAGE_ID,RACK_NO,ROW_NUMBER,COLUMN_NUMBER,FLOOR_NUMBER,CREATE_TIME) 
            VALUES  (@str, @rackNo, @k+1, @i+1, @j+1,GETDATE())  
    SET @j=@j+1   
    END 
    end
 SET @i=@i+1
END

set @k=@k+1
end

SOTRAGE_ID為了區(qū)分兩個(gè)庫(kù)設(shè)定了A庫(kù)和B庫(kù)

用list批量插入數(shù)據(jù)庫(kù)表數(shù)據(jù)

public ExecutionResult InsertPlan(List<EquipmentPlanLog> value)
        {
            string sql = @"INSERT INTO C_EQUIPMENT_MAINTAIN_PLAN_T (
             EQIP_ID
            ,EQIP_NAME
            ,EXPECT_MAINTAIN_DATE
            ,PERSON
            ,EQ_STATE
            ,REASON
            ,REMARK
            ,CREATE_TIME)
            VALUES ";

            foreach (var item in value)
            {
                string str = " ('{0}','{1}','{2}','{3}','{4}','{5}','{6}',GETDATE()),";
                str = string.Format(str, item.EqipId, item.EqipName, item.MaintainDate, item.Person, item.EqState, item.Reason, item.Remark);
                sql = sql + str;
            }
            char[] chr = {','};
            sql = sql.TrimEnd(chr); //去掉sql語(yǔ)句最后的 “ , ”
            var exeResult = sqlHelper.ExecuteQueryDS(sql);
            return exeResult;
        }