sqlserver基础

* 批量修改

1、完整实例

- 前台index.cshtml，注意使用ajax要用Guget添加ajax包，使用layer要添加layer.css和layer.js文件

<form style="MARGIN-TOP: 60PX">
    <div class="row">
        @Html.Label("姓名：")@Html.TextBox("name")
    </div>
    <div class="row">
        @Html.Label("年龄：")@Html.TextBox("age")
    </div>
    <div class="row">
        @Html.Label("性别：") &nbsp; @Html.Label("女")@Html.RadioButton("sex", "女") @Html.Label("男")@Html.RadioButton("sex", "男")
    </div>
    <div calss="row">
        <input type="button" value="提交" onclick="Save()" />
    </div>
</form>
<div id="result" />

@section Scripts{
    <script>
        function Save() {
            $.ajax({
                type: 'post',
                url: '@Url.Action("Save", "DB")',
                data: $("form").serialize(),  //将表单数据转化为json字符串
                success: function (data) {
                    $("#result").html(data);
                    layer.msg("成功！");
                },
                error: function () {
                    layer.msg("错误！")
                }
            });
        }
    </script>
}

-后台代码：

[HttpPost]
        public ActionResult Save(string name, int age, string sex)
        {
            DataTable dtAdd = new DataTable();
            dtAdd.Columns.Add("name");
            dtAdd.Columns.Add("age");
            dtAdd.Columns.Add("sex");

            var dr = dtAdd.NewRow(); //先新增一行
            dr["name"] = name;
            dr["age"] = age;
            dr["sex"] = sex;
            dtAdd.Rows.Add(dr); //将新增的一行添加到DataTable中

            var flag = false;
            if(dtAdd.Rows.Count > 0)  //如果有数据才新增
                flag = SqlHelper.AddBulkCopy(dtAdd);

            //返回JosnResult在API中自动有这个对象
            return new JsonResult() {ContentType="application/json",Data=flag };
        }

- 连接数据库类文件

private static readonly string Constr = ConfigurationManager.ConnectionStrings["hxh"].ToString();

/// <summary>
        /// 批量添加数据
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static bool AddBulkCopy(DataTable dt)
        {
            using (SqlConnection c = new SqlConnection(Constr))
            {
                c.Open(); //代开连接
                using (SqlTransaction tran = (SqlTransaction)c.BeginTransaction())
                {
                    using(SqlBulkCopy sqlbulkcopy = new SqlBulkCopy((SqlConnection)c, SqlBulkCopyOptions.KeepIdentity, tran))
                    {
                        sqlbulkcopy.DestinationTableName = "student"; //表名
                        for(int i=0; i<dt.Columns.Count; i++)
                        {
                            sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                        }
                        sqlbulkcopy.BatchSize = 50000;
                        try
                        {
                            sqlbulkcopy.WriteToServer(dt);
                            tran.Commit();
                            return true;
                        }
                        catch
                        {
                            tran.Rollback();
                            return false;
                        }
                    }
                }
                
            }
        }

-sqlserver连接字符串

<connectionStrings>
    <add name="hxh" connectionString="Server=DESKTOP-OCGH29Q;database=xx;integrated security=true;Connection Timeout=30;" providerName="System.Data.SqlClient" />
  </connectionStrings>

 <add name="Connection_Web_DB" connectionString="Server=cs6.tp33.net,14789;database=T21W;uid=Test_DF;pwd=FbZdbNT*Y9p1h6Tg;" providerName="System.Data.SqlClient" />

* sql语句

1、sql常用语句

-- 注意用了group by 之后，这里要与聚合函数联合使用，例如 max(f_id)
select k.* from(
        select min(d.f_type) as f_type, f_user, max(f_surplus) as f_surplus, min(f_checkTime) as f_checkTime, 
        min(f_errorMoney) as f_errorMoney, max(d.f_paytype)  as f_paytype, max(y.f_id)  as f_id,
        max(y.f_isExchange) as f_isExchange, max(y.f_state)  as f_state,
        count(case  when  isnull(d.f_state,0) = 1 then 1 end) as f_succNum ,    -- 新写法
        count(case  when isnull(d.f_state,0) <> 1 then 1 end) as f_faildNum,    -- 新写法
        count(case  when isnull(d.f_state,-1) = -1 then 1 end) as f_faildrz,
        count(case  when isnull(d.f_state,-1) = 0 then 1 end) as f_faildcl  
        from [dbo].[t_DeltaOnline] d with(nolock) 
        inner join 
        t_YeePayAccounts y with(nolock) on y.f_id = d.f_fkzl 
        where isnull(d.f_del,0)=0 and ISNULL(Y.f_del,0)<>1 and ISNULL(Y.f_del,0)<>2   -- ISNULL(Y.f_del,0)<>2
        group by f_user ) k where  k.f_user = '豪汇支付宝-测试商户'

  if exists
    (select * from t_Mypurse with(nolock) where f_payin = 1   and f_otherAccounts is not null and datediff(hh,f_time,getdate())< 48) select 1 
  else select 0

 DateDiff(dd,f_time,getdate())=0  -- 当天
 datediff(hh,f_time,getdate())< 48  --48小时内

-- charindex('', column) <=0 和 not like的意思一样，%匹配多个字符
select f_id, f_showname from t_Banks with(nolock) 
      where charindex('約',f_showname)<=0 and  f_MasterID=0 
      and (f_number<5000 or (f_number > 10000 and f_number <=11000))  and isnull(f_del,0)=0 
      and  (f_showname like '%[A-D]%'  or f_showname like '%F%' )   ORDER BY f_number ASC

-- 银行取款修改暂停分配, 0和1之间的切换
update t_admin set f_stop=1- isnull(f_stop,0) where f_accounts='' and isnull(f_del,0)=0

select * from t_ExchangeSet;
-- 根据类型获取自动讯息
 select f_id,f_msgType from t_autoMsgSet with(nolock)  where  f_type = 1 and f_target & 16 = 16 

-- 具有分笔的母单id
select f_id from t_exchange t where exists (
    select f_money,f_fee,f_status,f_serial,f_bookCode from t26w.dbo.t_subCash where f_serial = t.f_id and f_type=1 and f_status in (2, 3, 4, 6) 
) order by f_id asc;

SELECT Bank.f_ShowName as MemberBankName , BankLevel.f_majordomo ,BankLevel.f_StupeSurplus FROM 
    t_Banks_Level as BankLevel with(nolock) 
    left join 
    t_Banks as Bank with(nolock) 
    on BankLevel.f_BanksID=Bank.f_id 
    WHERE Bank.f_ShowName is not NULL and isnull(bank.f_del,0)=0 and isnull(f_Sort,0) <=100 
    AND (  -- or外层要用（）
            ( f_majordomo = 'F' AND f_StupeSurplus = 7000) OR( f_majordomo = 'F' AND f_StupeSurplus = 7000) OR
            ( f_majordomo = 'Q' AND f_StupeSurplus = 1001) OR( f_majordomo = '测试' AND f_StupeSurplus = 5001) OR
            ( f_majordomo = '测试' AND f_StupeSurplus = 5001) OR( f_majordomo = '测试' AND f_StupeSurplus = 5001)
    )   -- or外层要用（）

if exists (select f_id from t_banks ) select 1
else select 0;

use master;  -- 使用哪个数据库
select * from xx.dbo.BookInfo;  --从xx数据库查数据

CONVERT(data_type(length), data_to_be_converted, styleId); 

ISNULL（A, B）; 

2、dbLink的使用