* 批量修改
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("性别：") @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）;