12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000200120022003200420052006200720082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030203120322033203420352036203720382039204020412042204320442045204620472048204920502051205220532054205520562057205820592060206120622063206420652066206720682069207020712072207320742075207620772078207920802081208220832084208520862087208820892090209120922093209420952096209720982099210021012102210321042105210621072108210921102111211221132114211521162117211821192120212121222123212421252126212721282129213021312132213321342135213621372138213921402141214221432144214521462147214821492150215121522153215421552156215721582159216021612162216321642165216621672168216921702171217221732174217521762177217821792180218121822183218421852186218721882189219021912192219321942195219621972198219922002201220222032204220522062207220822092210221122122213221422152216221722182219222022212222222322242225222622272228222922302231223222332234223522362237223822392240224122422243224422452246224722482249225022512252225322542255225622572258225922602261226222632264226522662267226822692270227122722273227422752276227722782279228022812282228322842285228622872288228922902291229222932294229522962297229822992300230123022303230423052306230723082309231023112312231323142315231623172318231923202321232223232324232523262327232823292330233123322333233423352336233723382339234023412342234323442345234623472348234923502351235223532354235523562357235823592360236123622363236423652366236723682369237023712372237323742375237623772378237923802381238223832384238523862387238823892390239123922393239423952396239723982399240024012402240324042405240624072408240924102411241224132414241524162417241824192420242124222423242424252426242724282429243024312432243324342435243624372438243924402441244224432444244524462447244824492450245124522453245424552456245724582459246024612462246324642465246624672468246924702471247224732474247524762477247824792480248124822483248424852486248724882489249024912492249324942495249624972498249925002501250225032504250525062507250825092510251125122513251425152516251725182519252025212522252325242525252625272528252925302531253225332534253525362537253825392540254125422543254425452546254725482549255025512552255325542555255625572558255925602561256225632564256525662567256825692570257125722573257425752576257725782579258025812582258325842585258625872588258925902591259225932594259525962597259825992600260126022603260426052606260726082609261026112612261326142615261626172618261926202621262226232624262526262627262826292630263126322633263426352636263726382639264026412642264326442645264626472648264926502651265226532654265526562657265826592660266126622663266426652666266726682669267026712672267326742675267626772678267926802681268226832684268526862687268826892690269126922693269426952696269726982699270027012702270327042705270627072708270927102711271227132714271527162717271827192720272127222723272427252726272727282729273027312732273327342735273627372738273927402741274227432744274527462747274827492750275127522753275427552756275727582759276027612762276327642765276627672768276927702771277227732774277527762777277827792780278127822783278427852786278727882789279027912792279327942795279627972798279928002801280228032804280528062807280828092810281128122813281428152816281728182819282028212822282328242825282628272828282928302831283228332834283528362837283828392840284128422843284428452846284728482849285028512852285328542855285628572858285928602861286228632864286528662867286828692870287128722873287428752876287728782879288028812882288328842885288628872888288928902891289228932894289528962897289828992900290129022903290429052906 |
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Configuration;
- using System.Data.SqlClient;
- using System.Reflection;
- using System.Data;
- using System.Collections;
- using System.Web;
- using System.Web.Caching;
- public class SqlDbHelper
- {
- public static string constr = ConfigurationManager.AppSettings["MESProduction"].ToString();
- #region 验证数据库是否连接
- /// <summary>
- /// 测试数据库是否可以正常连接,time如果默认就不填写 如果填写则加timeout连接字符串
- /// </summary>
- /// <returns></returns>
- public static bool ChechSqlServer(int timeout)
- {
- string connnectstring = "";
- if (timeout != 0)
- {
- connnectstring = constr + ";Connect Timeout=" + timeout;
- }
- else { connnectstring = constr; }
- //constr里不要加;Connect Timeout=5000参数如果加参数此处需要修改
- bool succeed = false;
- try
- {
- SqlConnection p_connectionString = new SqlConnection(connnectstring);
- p_connectionString.Open();
- SqlCommand p_selectCommand = new SqlCommand("SELECT 1", p_connectionString);
- SqlDataReader reader = p_selectCommand.ExecuteReader();
- if (reader.HasRows)
- succeed = true;
- reader.Dispose();
- reader.Close();
- p_connectionString.Close();
- }
- catch
- { }
- return succeed;
- }
- #endregion
- #region 根据实体类名获取表名
- /// <summary>
- /// 根据实体类名返回数据库表名和其字段
- /// </summary>
- /// <typeparam name="T">实体类</typeparam>
- /// <param name="t">实体对象</param>
- /// <returns>数据表名和字段</returns>
- public static Dictionary<string, PropertyInfo[]> GetTableName<T>(T t)
- {
- if (t == null)
- {
- return null;
- }
- PropertyInfo[] propertys = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
- if (propertys.Length <= 0)
- {
- return null;
- }
- Dictionary<string, PropertyInfo[]> dic = new Dictionary<string, PropertyInfo[]>();
- string fname = t.ToString();
- string cname = typeof(T).Name;
- dic[cname] = propertys;
- return dic;
- }
- #endregion
- #region 增加一个记录
- /// <summary>
- /// 添加一条数据到数据库,排除主键在SQL中出现
- /// </summary>
- /// <typeparam name="T">数据实体类</typeparam>
- /// <param name="t">数据实体对象</param>
- /// <param name="constr">数据连接字符串</param>
- /// <returns>是否写入成功</returns>
- public static bool InsertModel<T>(T t, string constr)
- {
- SqlConnection conn = new SqlConnection(constr);
- string sql = string.Empty; SqlParameter[] sp = null;
- InsertSqlParams<T>(t, out sql, out sp);
- return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
- }
- /// <summary>
- /// 添加一条数据到数据库,排除主键在SQL中出现
- /// </summary>
- /// <typeparam name="T">数据实体类</typeparam>
- /// <param name="t">数据实体对象</param>
- /// <returns>是否写入成功</returns>
- public static bool InsertModel<T>(T t)
- {
- SqlConnection conn = new SqlConnection(constr);
- string sql = string.Empty; SqlParameter[] sp = null;
- InsertSqlParams<T>(t, out sql, out sp);
- return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
- }
- /// <summary>
- /// 添加一条数据到数据库,主键在SQL中出现
- /// </summary>
- /// <typeparam name="T">数据实体类</typeparam>
- /// <param name="t">数据实体对象</param>
- /// <param name="constr">数据连接字符串</param>
- /// <returns>是否写入成功</returns>
- public static bool InsertModelGuid<T>(T t)
- {
- SqlConnection conn = new SqlConnection(constr);
- string sql = string.Empty; SqlParameter[] sp = null;
- InsertSqlParamsNoParimary<T>(t, out sql, out sp, constr);
- return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
- }
- /// <summary>
- /// 添加一条数据到数据库,主键在SQL中出现
- /// </summary>
- /// <typeparam name="T">数据实体类</typeparam>
- /// <param name="t">数据实体对象</param>
- /// <param name="constr">数据连接字符串</param>
- /// <returns>是否写入成功</returns>
- public static bool InsertModelGuid<T>(T t, string constr)
- {
- SqlConnection conn = new SqlConnection(constr);
- string sql = string.Empty; SqlParameter[] sp = null;
- InsertSqlParamsNoParimary<T>(t, out sql, out sp, constr);
- return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
- }
- /// <summary>
- /// 添加一条数据到数据库,不进行数据表字段对比
- /// </summary>
- /// <typeparam name="T">数据实体类</typeparam>
- /// <param name="t">实体对象</param>
- /// <param name="constr">连接字符串</param>
- /// <returns>是否写入成功</returns>
- public static bool InsertModelnoEq<T>(T t, string constr)
- {
- SqlConnection conn = new SqlConnection(constr);
- string sql = string.Empty; SqlParameter[] sp = null;
- if (InsertSqlParamsnoEp<T>(t, out sql, out sp))
- {
- return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
- }
- return false;
- }
- /// <summary>
- /// 添加一条数据到数据库,并返回主标识id
- /// </summary>
- /// <typeparam name="T">数据实体类</typeparam>
- /// <param name="t">数据实体对象</param>
- /// <param name="id">数据成功插入后返回主标识id</param>
- /// <param name="constr">数据库连接字符串</param>
- /// <returns>数据插入是否成功</returns>
- public static bool InsertModel<T>(T t, out int id, string constr)
- {
- SqlConnection conn = new SqlConnection(constr);
- string sql = string.Empty; SqlParameter[] sp = null;
- InsertSqlParamsWithIdentity<T>(t, out sql, out sp);
- id = ExecuteNonQueryWithIdentity(conn, CommandType.Text, sql, sp);
- if (id > 0)
- {
- return true;
- }
- return false;
- }
- /// <summary>
- /// 插入一条数据并返回主键ID
- /// </summary>
- /// <typeparam name="T">数据实体</typeparam>
- /// <param name="t">实体参数</param>
- /// <param name="constr">数据库连接字符串</param>
- /// <returns>主键</returns>
- public static int InsertModelReturnIdentity<T>(T t, string constr)
- {
- SqlConnection conn = new SqlConnection(constr);
- string sql = string.Empty; SqlParameter[] sp = null;
- InsertSqlParamsWithIdentity<T>(t, out sql, out sp);
- return ExecuteNonQueryWithIdentity(conn, CommandType.Text, sql, sp);
- }
- public static bool InsertSqlParamsWithIdentity<T>(T t, out string sql, out SqlParameter[] paras)
- {
- sql = ""; paras = null;
- try
- {
- string cname = typeof(T).Name;
- //构造参数化SQL命令
- StringBuilder ExeSql = new StringBuilder();
- StringBuilder Params = new StringBuilder();
- ExeSql.Append("insert into " + cname + " (");
- //循环补充字段和参数
- List<SqlParameter> list = new List<SqlParameter>();
- List<CP.ColumnInfo> dic = CP.TC(cname, constr);
- if (dic == null || dic.Count == 0)
- {
- return false;
- }
- int counts = dic.Count;
- foreach (PropertyInfo pi in typeof(T).GetProperties())
- {
- CP.ColumnInfo item = dic.Find(p => p.ColName.ToLower() == pi.Name.ToLower());
- if (item != null)
- {
- if (!item.IsPrimary)
- {
- object v = pi.GetValue(t, null);
- if (v != null)//只对赋值了的属性进行操作
- {
- ExeSql.Append(pi.Name + ",");
- Params.Append("@" + pi.Name + ",");
- list.Add(CP.cPa(item, v));
- }
- }
- }
- }
- SqlParameter sp = new SqlParameter("@" + ParameterDirection.ReturnValue.ToString(), SqlDbType.Int);
- sp.Direction = ParameterDirection.Output;
- list.Add(sp);
- sql = ExeSql.ToString().TrimEnd(',') + ") values (" + Params.ToString().TrimEnd(',') + ");select @" + ParameterDirection.ReturnValue.ToString() + "=SCOPE_IDENTITY()";
- paras = list.ToArray();
- return true;
- }
- catch { return false; }
- }
- /// <summary>
- /// 循环T类型属性,表主键不插入方法
- /// </summary>
- /// <typeparam name="T"></typeparam>
- /// <param name="t"></param>
- /// <param name="sql"></param>
- /// <param name="paras"></param>
- /// <returns></returns>
- public static bool InsertSqlParams<T>(T t, out string sql, out SqlParameter[] paras)
- {
- sql = ""; paras = null;
- try
- {
- StringBuilder ExeSql = new StringBuilder();
- StringBuilder Params = new StringBuilder();
- string cname = typeof(T).Name;
- ExeSql.Append("insert into " + cname + " (");
- //循环补充字段和参数
- List<SqlParameter> list = new List<SqlParameter>();
- List<CP.ColumnInfo> dic = CP.TC(cname, constr);
- if (dic == null || dic.Count == 0)
- {
- return false;
- }
- foreach (PropertyInfo pi in typeof(T).GetProperties())
- {
- CP.ColumnInfo item = dic.Find(p => p.ColName.ToLower() == pi.Name.ToLower());
- if (item != null)
- {
- if (!item.IsPrimary)
- {
- object v = pi.GetValue(t, null);
- if (v != null)//只对赋值了的属性进行操作
- {
- ExeSql.Append(pi.Name + ",");
- Params.Append("@" + pi.Name + ",");
- list.Add(CP.cPa(item, v));
- }
- }
- }
- }
- sql = ExeSql.ToString().TrimEnd(',') + ") values (" + Params.ToString().TrimEnd(',') + ")";
- paras = list.ToArray();
- return true;
- }
- catch { return false; }
- }
- /// <summary>
- /// 循环T类型属性,表主键插入方法
- /// </summary>
- /// <typeparam name="T"></typeparam>
- /// <param name="t"></param>
- /// <param name="sql"></param>
- /// <param name="paras"></param>
- /// <returns></returns>
- public static bool InsertSqlParamsNoParimary<T>(T t, out string sql, out SqlParameter[] paras, string constr)
- {
- sql = ""; paras = null;
- try
- {
- StringBuilder ExeSql = new StringBuilder();
- StringBuilder Params = new StringBuilder();
- string cname = typeof(T).Name;
- ExeSql.Append("insert into " + cname + " (");
- //循环补充字段和参数
- List<SqlParameter> list = new List<SqlParameter>();
- List<CP.ColumnInfo> dic = CP.TC(cname, constr);
- if (dic == null || dic.Count == 0)
- {
- return false;
- }
- foreach (PropertyInfo pi in typeof(T).GetProperties())
- {
- CP.ColumnInfo item = dic.Find(p => p.ColName.ToLower() == pi.Name.ToLower());
- if (item != null)
- {
- object v = pi.GetValue(t, null);
- if (v != null)//只对赋值了的属性进行操作
- {
- ExeSql.Append(pi.Name + ",");
- Params.Append("@" + pi.Name + ",");
- list.Add(CP.cPa(item, v));
- }
- }
- }
- sql = ExeSql.ToString().TrimEnd(',') + ") values (" + Params.ToString().TrimEnd(',') + ")";
- paras = list.ToArray();
- return true;
- }
- catch { return false; }
- }
- /// <summary>
- /// 构造添加执行语句
- /// </summary>
- /// <typeparam name="T"></typeparam>
- /// <param name="t"></param>
- /// <param name="sql"></param>
- /// <param name="paras"></param>
- /// <returns>是否异常</returns>
- public static bool InsertSqlParamsnoEp<T>(T t, out string sql, out SqlParameter[] paras)
- {
- sql = ""; paras = null;
- StringBuilder ExeSql = new StringBuilder();
- StringBuilder Params = new StringBuilder();
- try
- {
- string cname = typeof(T).Name;
- List<SqlParameter> list = new List<SqlParameter>();
- ExeSql.Append("insert into " + cname + " (");
- foreach (PropertyInfo item in typeof(T).GetProperties())
- {
- object o = item.GetValue(t, null);
- if (o != null)
- {
- if (item.Name.ToLower() != "id")
- {
- ExeSql.Append(item.Name + ",");
- Params.Append("@" + item.Name + ",");
- list.Add(createParam(item, o));
- }
- }
- }
- sql = ExeSql.ToString().TrimEnd(',') + ")values(" + Params.ToString().TrimEnd(',') + ")";
- paras = list.ToArray();
- return true;
- }
- catch
- {
- return false;
- }
- }
- /// <summary>
- /// 添加一条数据到数据库
- /// </summary>
- /// <param name="Fields">表字段</param>
- /// <param name="Table">表名</param>
- /// <param name="constr">数据连接字符串</param>
- /// <returns>是否成功完成操作</returns>
- public static bool InsertRecord(Dictionary<string, object> Fields, string Table, string constr)
- {
- SqlConnection connection = new SqlConnection(constr);
- StringBuilder builder = new StringBuilder("");
- Table = Table.Replace("'", "");
- builder.Append("insert into " + Table + " (");
- IEnumerator<KeyValuePair<string, object>> enumerator = Fields.GetEnumerator();
- StringBuilder builder2 = new StringBuilder();
- List<SqlParameter> pitems = new List<SqlParameter>();
- while (enumerator.MoveNext())
- {
- KeyValuePair<string, object> current = enumerator.Current;
- string key = current.Key;
- object obj = current.Value;
- //只处理已赋值的项
- if (!string.IsNullOrEmpty(key) && (obj != null))
- {
- builder.Append(key + ",");
- builder2.Append("@" + key + ",");
- pitems.Add(createParam(key, obj));
- }
- }
- string sql = builder.ToString().TrimEnd(',') + ") values (" + builder2.ToString().TrimEnd(',') + ")";
- if (ExecuteNonQuery(connection, CommandType.Text, sql, pitems.ToArray()) > 0)
- return true;
- return false;
- }
- /// <summary>
- /// 添加一条数据到数据库
- /// </summary>
- /// <param name="Fields">表字段</param>
- /// <param name="Table">表名</param>
- /// <param name="id">数据插入后返回主标识(自动增长字段值)</param>
- /// <param name="constr">数据连接字符串</param>
- /// <returns>是否成功完成操作</returns>
- public static bool InsertRecord(Dictionary<string, object> Fields, string Table, out int id, string constr)
- {
- id = 0;
- SqlConnection connection = new SqlConnection(constr);
- StringBuilder builder = new StringBuilder("");
- Table = Table.Replace("'", "");
- builder.Append("insert into " + Table + " (");
- IEnumerator<KeyValuePair<string, object>> enumerator = Fields.GetEnumerator();
- StringBuilder builder2 = new StringBuilder();
- List<SqlParameter> pitems = new List<SqlParameter>();
- while (enumerator.MoveNext())
- {
- KeyValuePair<string, object> current = enumerator.Current;
- string key = current.Key;
- object obj = current.Value;
- //只处理已赋值的项
- if (!string.IsNullOrEmpty(key) && (obj != null))
- {
- builder.Append(key + ",");
- builder2.Append("@" + key + ",");
- pitems.Add(createParam(key, obj));
- }
- }
- SqlParameter sp = new SqlParameter("@out_return_id", SqlDbType.Int);
- sp.Direction = ParameterDirection.Output;
- pitems.Add(sp);
- string sql = builder.ToString().TrimEnd(',') + ") values (" + builder2.ToString().TrimEnd(',') + ") select @out_return_id=SCOPE_IDENTITY()";
- if (ExecuteNonQueryOutID(connection, CommandType.Text, sql, pitems.ToArray()) > 0)
- return true;
- return false;
- }
- #endregion
- #region 更新一个记录
- /// <summary>
- /// 更新一个记录,按主键
- /// </summary>
- /// <typeparam name="T">数据实体类</typeparam>
- /// <param name="t">数据实体对象</param>
- /// <param name="constr">数据库连接字符串</param>
- /// <returns></returns>
- public static bool UpdateModel<T>(T t, string constr)
- {
- SqlConnection conn = new SqlConnection(constr);
- string sql = string.Empty; SqlParameter[] sp = null;
- UpdateSqlParams<T>(t, out sql, out sp);
- return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
- }
- /// <summary>
- /// 更新某条记录,按主键 更新多个字段
- /// </summary>
- /// <typeparam name="T">数据实体类</typeparam>
- /// <param name="p">字典参数</param>
- /// <param name="constr">数据库连接字符串</param>
- /// <returns></returns>
- public static bool UpdateModel<T>(Dictionary<string, object> p, string constr)
- {
- SqlConnection conn = new SqlConnection(constr);
- string sql = string.Empty; SqlParameter[] sp = null;
- UpdateSqlParams<T>(p, out sql, out sp);
- return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
- }
- /// <summary>
- /// 更新某条记录,按主键 更新多个字段
- /// </summary>
- /// <typeparam name="T">数据实体类</typeparam>
- /// <param name="t">实体对象</param>
- /// <param name="constr">数据库连接字符串</param>
- /// <returns></returns>
- public static bool UpdateModelnoEp<T>(T t, string constr)
- {
- SqlConnection conn = new SqlConnection(constr);
- string sql = string.Empty; SqlParameter[] sp = null;
- if (UpdateSqlParamsnoEp<T>(t, out sql, out sp))
- {
- return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
- }
- return false;
- }
- public static bool UpdateSqlParams<T>(T t, out string sql, out SqlParameter[] paras)
- {
- sql = ""; paras = null;
- PropertyInfo[] propertys = typeof(T).GetProperties();
- string cname = typeof(T).Name;
- //构造参数化SQL命令
- StringBuilder ExeSql = new StringBuilder();
- ExeSql.Append("update " + cname + " set ");
- //循环补充字段和参数
- List<SqlParameter> list = new List<SqlParameter>();
- List<CP.ColumnInfo> dic = CP.TC(cname, constr);
- if (dic == null || dic.Count == 0)
- {
- return false;
- }
- string primaryKey = string.Empty;
- foreach (PropertyInfo pi in propertys)
- {
- var propertyType = Nullable.GetUnderlyingType(pi.PropertyType) ?? pi.PropertyType;//得到属性定义类型
- string t_p = propertyType.ToString().ToLower();//转化为小写
- t_p = t_p.Substring(t_p.IndexOf('.') + 1, t_p.Length - t_p.IndexOf('.') - 1);
- object v = pi.GetValue(t, null);
- if ((t_p == "string" || t_p == "char") && v == null)
- v = "";
- //object v = pi.GetValue(t, null);
- if (v != null)//只对赋值了的属性进行操作
- {
- CP.ColumnInfo item = dic.Find(p => p.ColName.ToLower() == pi.Name.ToLower());
- if (item != null)
- {
- if (!item.IsPrimary)
- {
- ExeSql.Append(pi.Name + "=@" + pi.Name + ",");
- }
- else
- {
- primaryKey = pi.Name;
- }
- list.Add(CP.cPa(item, v));
- }
- }
- }
- sql = ExeSql.ToString().TrimEnd(',') + " where " + primaryKey + "=@" + primaryKey;
- paras = list.ToArray();
- return true;
- }
- public static bool UpdateSqlParams<T>(Dictionary<string, object> p, out string sql, out SqlParameter[] paras)
- {
- sql = ""; paras = null;
- try
- {
- PropertyInfo[] propertys = typeof(T).GetProperties();
- string cname = typeof(T).Name;
- StringBuilder ExeSql = new StringBuilder();
- //构造参数化SQL命令
- ExeSql.Append("update " + cname + " set ");
- //循环补充字段和参数
- List<SqlParameter> list = new List<SqlParameter>();
- List<CP.ColumnInfo> dic = CP.TC(cname, constr);
- if (dic == null || dic.Count == 0)
- {
- return false;
- }
- //集合枚举
- IEnumerator<KeyValuePair<string, object>> enumerator = p.GetEnumerator();
- string primaryKey = string.Empty;//主键参数
- foreach (PropertyInfo pi in propertys)
- {
- while (enumerator.MoveNext())
- {
- KeyValuePair<string, object> current = enumerator.Current;
- string key = current.Key;
- object obj = current.Value;
- if (!string.IsNullOrEmpty(key) && (obj != null))
- {
- CP.ColumnInfo item = dic.Find(c => c.ColName.ToLower() == key.ToLower());
- if (item != null)
- {
- if (item.IsPrimary)
- {
- primaryKey = pi.Name;
- }
- else
- {
- ExeSql.Append(key + "=@" + key + ",");
- }
- list.Add(CP.cPa(item, obj));
- }
- }
- }
- }
- sql = ExeSql.ToString().TrimEnd(',') + " where " + primaryKey + "=@" + primaryKey;
- paras = list.ToArray();
- return true;
- }
- catch { return false; }
- }
- public static bool UpdateSqlParamsnoEp<T>(T t, out string sql, out SqlParameter[] paras)
- {
- sql = ""; paras = null;
- try
- {
- string cname = typeof(T).Name;
- //构造参数化SQL命令
- StringBuilder ExeSql = new StringBuilder();
- ExeSql.Append("update " + cname + " set ");
- //循环补充字段和参数
- List<SqlParameter> list = new List<SqlParameter>();
- string primaryKey = string.Empty; //主键
- foreach (PropertyInfo item in typeof(T).GetProperties())
- {
- object v = item.GetValue(t, null);
- if (v != null)
- {
- if (item.Name.ToLower() != "id")
- {
- ExeSql.Append(item.Name + "=@" + item.Name + ",");
- }
- else
- {
- primaryKey = item.Name + "=@" + item.Name;
- }
- list.Add(createParam(item, v));
- }
- }
- sql = ExeSql.ToString().TrimEnd(',') + " where " + primaryKey;
- paras = list.ToArray();
- return true;
- }
- catch { return false; }
- }
- #endregion
- #region 得到一个记录
- /// <summary>
- /// 根据主键标识id获取一个记录
- /// </summary>
- /// <typeparam name="T">数据实体类</typeparam>
- /// <param name="id">主键标识id</param>
- /// <param name="constr">数据库连接字符串</param>
- /// <returns>一个数据实体所有信息</returns>
- public static T GetModel<T>(int id, string constr) where T : new()
- {
- // 获得此模型的类型
- T t = new T();
- string sql = string.Empty; SqlParameter[] sp = null; PropertyInfo[] propertys = null;
- GetSqlParams<T>(id, out sql, out sp, out propertys);
- DataSet ds = ExecuteDataset(constr, CommandType.Text, sql, sp);
- if (ds.Tables.Count == 0)
- {
- return default(T);
- }
- DataTable dt = ds.Tables[0];
- if (dt.Rows.Count <= 0)
- {
- return default(T);
- }
- string tempName = "";
- foreach (DataRow dr in dt.Rows)
- {
- // 获得此模型的公共属性
- foreach (PropertyInfo pi in propertys)
- {
- tempName = pi.Name;
- // 检查DataTable是否包含此列
- if (dt.Columns.Contains(tempName))
- {
- // 判断此属性是否有Setter
- if (!pi.CanWrite) continue;
- object value = dr[tempName];
- if (value != DBNull.Value && value != null)
- pi.SetValue(t, value, null);
- }
- }
- }
- return t;
- }
- public static bool GetSqlParams<T>(int id, out string sql, out SqlParameter[] paras, out PropertyInfo[] py)
- {
- sql = ""; paras = null;
- //获取数据实体信息
- string cname = typeof(T).Name;
- py = typeof(T).GetProperties();
- List<SqlParameter> list = new List<SqlParameter>();
- List<CP.ColumnInfo> dic = CP.TC(cname, constr);
- string searchCols = string.Empty;
- string primaryKey = string.Empty;
- foreach (PropertyInfo pi in py)
- {
- CP.ColumnInfo item = dic.Find(p => p.ColName.ToLower() == pi.Name.ToLower());
- if (item != null)
- {
- if (item.IsPrimary)
- {
- primaryKey = item.ColName;
- list.Add(CP.cPa(item, id));
- }
- searchCols += pi.Name + ",";
- }
- }
- searchCols = searchCols.TrimEnd(',');
- sql = "select " + searchCols + " from " + cname + " where " + primaryKey + "=@" + primaryKey;
- paras = list.ToArray();
- return true;
- }
- public static bool GetSqlParams<T>(int id, out string sql, out SqlParameter[] paras)
- {
- sql = ""; paras = null;
- // 获得此模型的类型
- string cname = typeof(T).Name;
- //获取数据实体信息
- List<SqlParameter> list = new List<SqlParameter>();
- List<CP.ColumnInfo> dic = CP.TC(cname, constr);
- if (dic == null || dic.Count == 0) { return false; }
- string searchCols = string.Empty;
- string primaryKey = string.Empty;
- foreach (PropertyInfo pi in typeof(T).GetProperties())
- {
- CP.ColumnInfo item = dic.Find(p => p.ColName.ToLower() == pi.Name.ToLower());
- if (item != null)
- {
- if (item.IsPrimary)
- {
- primaryKey = item.ColName;
- list.Add(CP.cPa(item, id));
- }
- searchCols += pi.Name + ",";
- }
- }
- searchCols = searchCols.TrimEnd(',');
- sql = "select " + searchCols + " from " + cname + " where " + primaryKey + "=@" + primaryKey;
- paras = list.ToArray();
- return true;
- }
- /// <summary>
- /// 根据一个或多个实体赋值字段做为查询条件得到一个新的实体
- /// </summary>
- /// <typeparam name="T">数据实体类</typeparam>
- /// <param name="t">数据实体对象</param>
- /// <param name="constr">数据库连接字符串</param>
- /// <returns>一个数据实体所有信息</returns>
- public static T GetModel<T>(IDictionary<string, object> p, string constr) where T : new()
- {
- string sql = ""; SqlParameter[] sp = null; PropertyInfo[] propertys = null;
- T t = new T();
- GetSqlParams<T>(p, out sql, out sp, out propertys);
- DataSet ds = ExecuteDataset(constr, CommandType.Text, sql, sp);
- if (ds.Tables.Count == 0)
- {
- return default(T);
- }
- DataTable dt = ds.Tables[0];
- if (dt.Rows.Count <= 0)
- {
- return default(T);
- }
- string tempName = "";
- foreach (DataRow dr in dt.Rows)
- {
- // 获得此模型的公共属性
- foreach (PropertyInfo pi in propertys)
- {
- tempName = pi.Name;
- // 检查DataTable是否包含此列
- if (dt.Columns.Contains(tempName))
- {
- // 判断此属性是否有Setter
- if (!pi.CanWrite) continue;
- object value = dr[tempName];
- if (value != DBNull.Value && value != null)
- pi.SetValue(t, value, null);
- }
- }
- }
- return t;
- }
- public static bool GetSqlParams<T>(IDictionary<string, object> p, out string sql, out SqlParameter[] paras)
- {
- sql = ""; paras = null;
- string cname = typeof(T).Name;
- //获取数据实体信息
- List<SqlParameter> list = new List<SqlParameter>();
- List<CP.ColumnInfo> dic = null;
- //判断T是否为视图
- dic = cname.StartsWith("V") ? CP.TCV(cname, constr) : CP.TC(cname, constr);
- string searchCols = string.Empty;
- string primaryKey = string.Empty;
- string temp = "";
- bool Isfirst = true;
- foreach (PropertyInfo pi in typeof(T).GetProperties())
- {
- CP.ColumnInfo item = dic.Find(c => c.ColName.ToLower() == pi.Name.ToLower());
- if (item != null)
- {
- searchCols += pi.Name + ",";
- }
- }
- //检查查询字典,如果为空,直接返回
- if (p != null && p.Count != 0)
- {
- IEnumerator<KeyValuePair<string, object>> enumera = p.GetEnumerator();
- while (enumera.MoveNext())
- {
- KeyValuePair<string, object> current = enumera.Current;
- string key = current.Key;
- object v = current.Value;
- if (!string.IsNullOrEmpty(key) && (v != null))
- {
- CP.ColumnInfo item = dic.Find(m => m.ColName.ToLower() == key.ToLower());
- if (item != null)
- {
- list.Add(CP.cPa(item, v));
- if (Isfirst)
- {
- temp += item.ColName + "=@" + item.ColName;
- Isfirst = false;
- }
- else
- {
- temp += " and " + item.ColName + "=@" + item.ColName;
- }
- }
- }
- }
- }
- else
- {
- temp = "1=1";
- }
- searchCols = searchCols.TrimEnd(',');
- sql = "select " + searchCols + " from " + cname + " where " + temp;
- paras = list.ToArray();
- return true;
- }
- public static bool GetSqlParams<T>(IDictionary<string, object> p, out string sql, out SqlParameter[] paras, out PropertyInfo[] py)
- {
- sql = ""; paras = null;
- py = typeof(T).GetProperties();
- string cname = typeof(T).Name;
- //获取数据实体信息
- List<SqlParameter> list = new List<SqlParameter>();
- List<CP.ColumnInfo> dic = CP.TC(cname, constr);
- string searchCols = string.Empty;
- string primaryKey = string.Empty;
- string temp = "";
- bool Isfirst = true;
- foreach (PropertyInfo pi in py)
- {
- CP.ColumnInfo item = dic.Find(c => c.ColName.ToLower() == pi.Name.ToLower());
- if (item != null)
- {
- searchCols += pi.Name + ",";
- }
- }
- //检查查询字典,如果为空,直接返回
- if (p == null || p.Count == 0) { return false; }
- IEnumerator<KeyValuePair<string, object>> enumera = p.GetEnumerator();
- while (enumera.MoveNext())
- {
- KeyValuePair<string, object> current = enumera.Current;
- string key = current.Key;
- object v = current.Value;
- if (!string.IsNullOrEmpty(key) && (v != null))
- {
- CP.ColumnInfo item = dic.Find(m => m.ColName.ToLower() == key.ToLower());
- if (item != null)
- {
- list.Add(CP.cPa(item, v));
- if (Isfirst)
- {
- temp += item.ColName + "=@" + item.ColName;
- Isfirst = false;
- }
- else
- {
- temp += " and " + item.ColName + "=@" + item.ColName;
- }
- }
- }
- }
- searchCols = searchCols.TrimEnd(',');
- sql = "select " + searchCols + " from " + cname + " where " + temp;
- paras = list.ToArray();
- return true;
- }
- #endregion
- #region 删除一个记录
- /// <summary>
- /// 删除一个记录
- /// </summary>
- /// <param name="id">标示ID</param>
- /// <param name="tableName">数据表名</param>
- /// <returns>成功失败</returns>
- public static bool DelSingle(string sql, string constr)
- {
- using (SqlConnection conn = new SqlConnection(constr))
- {
- SqlCommand cmd = new SqlCommand();
- PrepareCommand(cmd, conn, null, CommandType.Text, sql, null);
- int count = 0;
- try
- {
- count = cmd.ExecuteNonQuery();
- }
- catch (Exception e)
- {
- throw e;
- }
- finally
- {
- CloseConnection(conn);
- }
- if (count > 0)
- return true;
- return false;
- }
- }
- /// <summary>
- /// 从数据库表中删除一个记录,按主键标识id
- /// </summary>
- /// <typeparam name="T">数据实体类</typeparam>
- /// <param name="t">数据实体对象</param>
- /// <param name="constr">数据连接字符串</param>
- /// <returns>是否删除成功</returns>
- public static bool DeleteModel<T>(T t, string constr)
- {
- if (t == null)
- {
- return false;
- }
- // 获得此模型的公共属性
- PropertyInfo[] propertys = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
- if (propertys.Length <= 0)
- {
- return false;
- }
- string fname = t.ToString();
- string cname = fname.Substring(fname.LastIndexOf(".") + 1, fname.Length - fname.LastIndexOf(".") - 1);
- bool result = false;
- foreach (PropertyInfo pi in propertys)
- {
- object v = pi.GetValue(t, null);
- if (v != null)//只对赋值了的属性进行操作,
- {
- if (pi.Name.ToString().ToLower() == "id")
- {
- SqlParameter[] pitems = { new SqlParameter("@" + pi.Name, SqlDbType.Int) };
- pitems[0].Value = int.Parse(v.ToString());
- string sql = "delete from " + cname + " where id=@" + pi.Name + "";
- SqlConnection conn = new SqlConnection(constr);
- if (ExecuteNonQuery(conn, CommandType.Text, sql, pitems) > 0)
- {
- result = true;
- }
- break;
- }
- }
- }
- return result;
- }
- /// <summary>
- /// 根据主键ID删除一条记录,注:主键必须是ID,且存在
- /// </summary>
- /// <typeparam name="T">删除实体</typeparam>
- /// <param name="id">ID</param>
- /// <param name="constr">数据库连接</param>
- /// <returns>成功失败</returns>
- public static bool DeleteModel<T>(int id, string constr) where T : new()
- {
- if (id == 0)
- return false;
- string cname = typeof(T).Name;
- string sql = "delete from " + cname + " where id=@id";
- SqlParameter para = new SqlParameter("@id", id);
- SqlConnection conn = new SqlConnection(constr);
- if (ExecuteNonQuery(conn, CommandType.Text, sql, para) > 0)
- return true;
- return false;
- }
- /// <summary>
- /// 删除多条记录,按条件
- /// </summary>
- /// <typeparam name="T">要删除的实体类</typeparam>
- /// <param name="p">键值对</param>
- /// <param name="constr">数据库连接</param>
- /// <returns>成功失败</returns>
- public static bool DeleteModel<T>(IDictionary<string, object> p, string constr)
- {
- if (p == null || p.Count == 0)
- return false;
- string tablename = typeof(T).Name;
- StringBuilder ExeSql = new StringBuilder();
- ExeSql.Append("delete from " + tablename);
- IEnumerator<KeyValuePair<string, object>> enumerator = p.GetEnumerator();
- List<SqlParameter> list = new List<SqlParameter>();
- bool isFirst = true;
- while (enumerator.MoveNext())
- {
- KeyValuePair<string, object> current = enumerator.Current;
- string key = current.Key;
- object obj = current.Value;
- if (!string.IsNullOrEmpty(key) && (obj != null))
- {
- if (isFirst)
- {
- ExeSql.Append(" where " + key + "=@" + key);
- isFirst = false;
- }
- else
- {
- ExeSql.Append(" and " + key + "=@" + key);
- }
- list.Add(createParam(key, obj));
- }
- }
- SqlConnection conn = new SqlConnection(constr);
- try
- {
- if (ExecuteNonQuery(conn, CommandType.Text, ExeSql.ToString(), list.ToArray()) > 0)
- return true;
- return false;
- }
- catch { return false; }
- }
- #endregion
- #region 得到多个记录
- /// <summary>
- /// 根据ID得到多个记录
- /// </summary>
- /// <typeparam name="T"></typeparam>
- /// <param name="sql">SQL查询命令</param>
- /// <param name="constr">数据库连接</param>
- /// <param name="paras">可变参数</param>
- /// <returns></returns>
- public static List<T> GetMoreModel<T>(string sql, string constr, params SqlParameter[] paras) where T : new()
- {
- List<T> list = new List<T>();
- DataSet ds = Query(sql, paras);
- int count = ds.Tables[0].Rows.Count;
- if (count == 0)
- {
- return null;
- }
- string tempName = "";
- foreach (DataRow dr in ds.Tables[0].Rows)
- {
- T v = new T();
- // 获得此模型的公共属性
- foreach (PropertyInfo pi in typeof(T).GetProperties())
- {
- tempName = pi.Name;
- // 检查DataTable是否包含此列
- if (ds.Tables[0].Columns.Contains(tempName))
- {
- // 判断此属性是否有Setter
- if (!pi.CanWrite) continue;
- object value = dr[tempName];
- if (value != DBNull.Value && value != null)
- pi.SetValue(v, value, null);
- }
- }
- list.Add(v);
- }
- return list;
- }
- /// <summary>
- /// 根据键值对获取多个记录
- /// </summary>
- /// <typeparam name="T">类型</typeparam>
- /// <param name="p">条件</param>
- /// <param name="constr">数据库连接</param>
- /// <param name="param">参数</param>
- /// <returns></returns>
- public static List<T> GetMoreModel<T>(Dictionary<string, object> p, string constr, params SqlParameter[] param) where T : new()
- {
- List<T> list = new List<T>();
- string sql = string.Empty; SqlParameter[] sp = null;
- GetSqlParams<T>(p, out sql, out sp);
- DataSet ds = ExecuteDataset(constr, CommandType.Text, sql, sp);
- int count = ds.Tables[0].Rows.Count;
- if (count == 0)
- {
- return null;
- }
- string tempName = "";
- foreach (DataRow dr in ds.Tables[0].Rows)
- {
- T v = new T();
- // 获得此模型的公共属性
- foreach (PropertyInfo pi in typeof(T).GetProperties())
- {
- tempName = pi.Name;
- // 检查DataTable是否包含此列
- if (ds.Tables[0].Columns.Contains(tempName))
- {
- // 判断此属性是否有Setter
- if (!pi.CanWrite) continue;
- object value = dr[tempName];
- if (value != DBNull.Value && value != null)
- pi.SetValue(v, value, null);
- }
- }
- list.Add(v);
- }
- return list;
- }
- /// <summary>
- /// 根据键值对获取多个记录
- /// </summary>
- /// <typeparam name="T">类型</typeparam>
- /// <param name="p">条件</param>
- /// <param name="sort">条件排序 id desc</param>
- /// <param name="constr">数据库连接</param>
- /// <param name="param">参数</param>
- /// <returns></returns>
- //public static List<T> GetMoreModel<T>(Dictionary<string, object> p,string sort, string constr, params SqlParameter[] param) where T : new()
- //{
- // List<T> list = new List<T>();
- // string sql = string.Empty; SqlParameter[] sp = null;
- // GetSqlParams<T>(p, out sql, out sp);
- // if (!string.IsNullOrEmpty(sort)) { sql += " order by "+sort; }
- // DataSet ds = ExecuteDataset(constr, CommandType.Text, sql, sp);
- // int count = ds.Tables[0].Rows.Count;
- // if (count == 0)
- // {
- // return null;
- // }
- // string tempName = "";
- // foreach (DataRow dr in ds.Tables[0].Rows)
- // {
- // T v = new T();
- // // 获得此模型的公共属性
- // foreach (PropertyInfo pi in typeof(T).GetProperties())
- // {
- // tempName = pi.Name;
- // // 检查DataTable是否包含此列
- // if (ds.Tables[0].Columns.Contains(tempName))
- // {
- // // 判断此属性是否有Setter
- // if (!pi.CanWrite) continue;
- // object value = dr[tempName];
- // if (value != DBNull.Value && value != null)
- // pi.SetValue(v, value, null);
- // }
- // }
- // list.Add(v);
- // }
- // return list;
- //}
- #endregion
- #region 根据SQL获取单值object
- /// <summary>
- /// 执行一条SQL语句,返回查询结果(object)。
- /// </summary>
- /// <param name="SQLString">SQL命令</param>
- /// <param name="constr">数据库连接串</param>
- /// <returns>查询结果(object)</returns>
- public static object GetSingle(string SQLString, string constr)
- {
- using (SqlConnection connection = new SqlConnection(constr))
- {
- SqlCommand cmd = new SqlCommand();
- PrepareCommand(cmd, connection, null, CommandType.Text, SQLString, null);
- try
- {
- object obj = cmd.ExecuteScalar();
- if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
- {
- return null;
- }
- else
- {
- return obj;
- }
- }
- catch (Exception e)
- {
- throw e;
- }
- finally
- {
- CloseConnection(connection);
- }
- }
- }
- /// <summary>
- /// 执行一条计算查询结果语句,返回查询结果(object)。
- /// </summary>
- /// <param name="SQLString">计算查询结果语句</param>
- /// <returns>查询结果(object)</returns>
- public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
- {
- using (SqlConnection connection = new SqlConnection(constr))
- {
- using (SqlCommand cmd = new SqlCommand())
- {
- try
- {
- PrepareCommand(cmd, connection, null, CommandType.Text, SQLString, cmdParms);
- object obj = cmd.ExecuteScalar();
- cmd.Parameters.Clear();
- if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
- {
- return null;
- }
- else
- {
- return obj;
- }
- }
- catch (System.Data.SqlClient.SqlException e)
- {
- throw e;
- }
- finally
- {
- CloseConnection(connection);
- }
- }
- }
- }
- /// <summary>
- /// 执行一条计算查询结果语句,返回查询结果(object)。
- /// </summary>
- /// <param name="connection">SqlConnection对象</param>
- /// <param name="trans">SqlTransaction事务</param>
- /// <param name="SQLString">计算查询结果语句</param>
- /// <returns>查询结果(object)</returns>
- public static object GetSingle(SqlConnection connection, SqlTransaction trans, string SQLString, params SqlParameter[] cmdParms)
- {
- using (SqlCommand cmd = new SqlCommand())
- {
- try
- {
- PrepareCommand(cmd, connection, trans, CommandType.Text, SQLString, cmdParms);
- object obj = cmd.ExecuteScalar();
- cmd.Parameters.Clear();
- if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
- {
- return null;
- }
- else
- {
- return obj;
- }
- }
- catch (System.Data.SqlClient.SqlException e)
- {
- trans.Rollback();
- throw e;
- }
- finally
- {
- CloseConnection(connection);
- }
- }
- }
- /// <summary>
- /// 是否存在该记录
- /// </summary>
- /// <param name="strSql">语句</param>
- /// <param name="cmdParms">参数</param>
- /// <returns>结果</returns>
- public static bool Exists(string strSql, params SqlParameter[] cmdParms)
- {
- object obj = GetSingle(strSql, cmdParms);
- int cmdresult;
- if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
- {
- cmdresult = 0;
- }
- else
- {
- cmdresult = int.Parse(obj.ToString());
- }
- if (cmdresult == 0)
- {
- return false;
- }
- else
- {
- return true;
- }
- }
- #endregion
- #region 根据SQL语句执行SQL指令 返回int
- /// <summary>
- /// 执行单纯的SQL语句
- /// </summary>
- /// <param name="SQLString">SQL语句</param>
- /// <param name="constr">数据库连接</param>
- /// <returns>结果(int)</returns>
- public static int ExecuteCmd(string SQLString, string constr)
- {
- int result = 0;
- using (SqlConnection conn = new SqlConnection(constr))
- {
- try
- {
- SqlCommand cmd = new SqlCommand();
- PrepareCommand(cmd, conn, null, CommandType.Text, SQLString, null);
- result = cmd.ExecuteNonQuery();
- }
- catch (Exception e)
- {
- throw e;
- }
- finally
- {
- CloseConnection(conn);
- }
- return result;
- }
- }
- /// <summary>
- /// 执行SQL指令带参数
- /// </summary>
- /// <param name="SQLString">SQL语句</param>
- /// <param name="constr">数据库连接字符串</param>
- /// <param name="para">参数</param>
- /// <returns>结果(int)</returns>
- public static int ExecuteCmd(string SQLString, string constr, params SqlParameter[] para)
- {
- int result = 0;
- using (SqlConnection conn = new SqlConnection(constr))
- {
- SqlCommand cmd = new SqlCommand();
- try
- {
- PrepareCommand(cmd, conn, null, CommandType.Text, SQLString, para);
- result = cmd.ExecuteNonQuery();
- cmd.Parameters.Clear();
- }
- catch (Exception e)
- {
- throw e;
- }
- finally
- {
- CloseConnection(conn);
- }
- return result;
- }
- }
- #endregion
- #region 执行Sql命令
- /// <summary>
- /// 执行Cmd
- /// </summary>
- /// <param name="connection">连接对象</param>
- /// <param name="commandType">类型</param>
- /// <param name="commandText">内容</param>
- /// <param name="commandParameters">参数</param>
- /// <returns>结果</returns>
- public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
- {
- SqlCommand cmd = new SqlCommand();
- PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
- int retval = 0;
- try
- {
- retval = cmd.ExecuteNonQuery();
- cmd.Parameters.Clear();
- }
- catch (Exception ee)
- {
- throw ee;
- }
- finally
- {
- CloseConnection(connection);
- }
- return retval;
- }
- /// <summary>
- /// 执行cmd命令返回主键 ID
- /// </summary>
- /// <param name="connection"></param>
- /// <param name="commandType"></param>
- /// <param name="commandText"></param>
- /// <param name="commandParameters"></param>
- /// <returns></returns>
- public static int ExecuteNonQueryWithIdentity(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
- {
- SqlCommand cmd = new SqlCommand();
- PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
- int retval = 0;
- try
- {
- cmd.ExecuteNonQuery();
- retval = int.Parse(cmd.Parameters["@" + ParameterDirection.ReturnValue.ToString()].Value.ToString());
- cmd.Parameters.Clear();
- }
- catch (Exception e)
- {
- throw e;
- }
- finally
- {
- CloseConnection(connection);
- }
- return retval;
- }
- /// <summary>
- /// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection
- /// using the provided parameters.
- /// </summary>
- /// <remarks>
- /// e.g.:
- /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
- /// </remarks>
- /// <param name="connection">A valid SqlConnection</param>
- /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
- /// <param name="commandText">The stored procedure name or T-SQL command</param>
- /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
- /// <returns>An int representing the number of rows affected by the command</returns>
- public static int ExecuteNonQueryOutID(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
- {
- SqlCommand cmd = new SqlCommand();
- PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
- int retval = 0;
- try
- {
- cmd.ExecuteNonQuery();
- retval = int.Parse(cmd.Parameters["@out_return_id"].Value.ToString());
- cmd.Parameters.Clear();
- }
- catch (Exception e)
- {
- throw e;
- }
- finally
- {
- CloseConnection(connection);
- }
- return retval;
- }
- /// <summary>
- /// 返回带返回参数的过程
- /// </summary>
- /// <param name="connection">数据库连接对象</param>
- /// <param name="commandType">数据类型</param>
- /// <param name="commandText">存储过程名或SQL语句</param>
- /// <param name="commandParameters">参数</param>
- /// <returns>参数</returns>
- public static SqlParameter[] ExecuteNonQueryOutPara(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
- {
- SqlCommand cmd = new SqlCommand();
- SqlParameter[] para = null;
- PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
- try
- {
- cmd.ExecuteNonQuery();
- if (commandParameters != null)
- {
- para = commandParameters;
- for (int i = 0; i < commandParameters.Length; i++)
- {
- if (commandParameters[i].Direction == ParameterDirection.Output || commandParameters[i].Direction == ParameterDirection.ReturnValue)
- {
- para[i].ParameterName = commandParameters[i].ParameterName;
- para[i].Value = commandParameters[i].Value;
- }
- }
- }
- cmd.Parameters.Clear();
- }
- catch (Exception e)
- {
- throw e;
- }
- finally
- {
- CloseConnection(connection);
- }
- return para;
- }
- /// <summary>
- /// 事务处理,执行SQL语句,返回影响的记录数,只执行一个命令,不关闭连接,连接关闭在调用端执行
- /// </summary>
- /// <param name="connection">SqlConnection对象</param>
- /// <param name="trans">SqlTransaction事件</param>
- /// <param name="SQLString">SQL语句</param>
- /// <returns>影响的记录数</returns>
- public static int ExecuteSql(SqlConnection connection, SqlTransaction trans, string SQLString)
- {
- int rows = 0;
- using (SqlCommand cmd = new SqlCommand())
- {
- PrepareCommand(cmd, connection, trans, CommandType.Text, SQLString, null);
- try
- {
- rows = cmd.ExecuteNonQuery();
- }
- catch
- {
- trans.Rollback();
- }
- finally
- {
- CloseConnection(connection);
- }
- return rows;
- }
- }
- /// <summary>
- /// 事务处理,执行SQL语句,返回影响的记录数,连接关闭在调用端执行
- /// </summary>
- /// <param name="connection">SqlConnection对象</param>
- /// <param name="trans">SqlTransaction事务</param>
- /// <param name="commandType">Sql命令类型</param>
- /// <param name="SQLString">Sql查询语句</param>
- /// <param name="cmdParms">Sql命令参数数组</param>
- /// <returns>影响的记录数</returns>
- public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
- {
- if (transaction == null) throw new ArgumentNullException("transaction");
- if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
- // Create a command and prepare it for execution
- SqlCommand cmd = new SqlCommand();
- PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
- // Finally, execute the command
- int retval = 0;
- try
- {
- retval = cmd.ExecuteNonQuery();
- // Detach the SqlParameters from the command object, so they can be used again
- cmd.Parameters.Clear();
- }
- catch (Exception e)
- {
- transaction.Rollback();
- throw e;
- }
- finally
- {
- CloseConnection(transaction.Connection);
- }
- return retval;
- }
- /// <summary>
- /// 执行多条SQL语句,实现数据库事务。
- /// </summary>
- /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param>
- public static void ExecuteSqlTranWithIndentity(List<CommandInfo> SQLStringList, out int identity)
- {
- using (SqlConnection conn = new SqlConnection(constr))
- {
- conn.Open();
- using (SqlTransaction trans = conn.BeginTransaction())
- {
- SqlCommand cmd = new SqlCommand();
- try
- {
- identity = 0;
- //循环
- foreach (CommandInfo myDE in SQLStringList)
- {
- string cmdText = myDE.CommandText;
- SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
- foreach (SqlParameter q in cmdParms)
- {
- if (q.Direction == ParameterDirection.InputOutput)
- {
- q.Value = identity;
- }
- }
- PrepareCommand(cmd, conn, trans, CommandType.Text, cmdText, cmdParms);
- int val = cmd.ExecuteNonQuery();
- foreach (SqlParameter q in cmdParms)
- {
- if (q.Direction == ParameterDirection.Output)
- {
- identity = Convert.ToInt32(q.Value);
- }
- }
- cmd.Parameters.Clear();
- }
- trans.Commit();
- }
- catch
- {
- trans.Rollback();
- throw;
- }
- finally
- {
- CloseConnection(conn);
- }
- }
- }
- }
- /// <summary>
- /// 执行多条SQL语句,实现数据库事务。
- /// </summary>
- /// <param name="SQLStringList">SQL命令集合</param>
- public static int ExecuteSqlTran(List<CommandInfo> cmdList)
- {
- using (SqlConnection conn = new SqlConnection(constr))
- {
- conn.Open();
- using (SqlTransaction trans = conn.BeginTransaction())
- {
- SqlCommand cmd = new SqlCommand();
- try
- {
- int count = 0;
- //循环
- foreach (CommandInfo myDE in cmdList)
- {
- string cmdText = myDE.CommandText;
- SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
- PrepareCommand(cmd, conn, trans, CommandType.Text, cmdText, cmdParms);
- int val = cmd.ExecuteNonQuery();
- count += val;
- cmd.Parameters.Clear();
- }
- trans.Commit();
- return count;
- }
- catch
- {
- trans.Rollback();
- throw;
- }
- finally
- {
- CloseConnection(conn);
- }
- }
- }
- }
- /// <summary>
- /// 执行多条SQL语句,实现数据库事务。
- /// </summary>
- /// <param name="SQLStringList">多条SQL语句</param>
- public static int ExecuteSqlTran(List<String> SQLStringList)
- {
- using (SqlConnection conn = new SqlConnection(constr))
- {
- conn.Open();
- SqlCommand cmd = new SqlCommand();
- cmd.Connection = conn;
- SqlTransaction tx = conn.BeginTransaction();
- cmd.Transaction = tx;
- try
- {
- int count = 0;
- for (int n = 0; n < SQLStringList.Count; n++)
- {
- string strsql = SQLStringList[n];
- if (strsql.Trim().Length > 1)
- {
- cmd.CommandText = strsql;
- count += cmd.ExecuteNonQuery();
- }
- }
- tx.Commit();
- return count;
- }
- catch
- {
- tx.Rollback();
- return 0;
- }
- finally
- {
- CloseConnection(conn);
- }
- }
- }
- /// <summary>
- /// 执行SQL语句,返回影响的记录数
- /// </summary>
- /// <param name="SQLString">SQL语句</param>
- /// <returns>影响的记录数</returns>
- public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
- {
- using (SqlConnection connection = new SqlConnection(constr))
- {
- int rows = 0;
- using (SqlCommand cmd = new SqlCommand())
- {
- try
- {
- PrepareCommand(cmd, connection, null, CommandType.Text, SQLString, cmdParms);
- rows = cmd.ExecuteNonQuery();
- cmd.Parameters.Clear();
- }
- catch (System.Data.SqlClient.SqlException e)
- {
- throw e;
- }
- finally
- {
- CloseConnection(connection);
- }
- }
- return rows;
- }
- }
- /// <summary>
- /// 执行SQL语句,返回影响行数
- /// </summary>
- /// <param name="commandType">操作类型</param>
- /// <param name="commandText">SQL语句</param>
- /// <param name="constr">数据库连接字符串</param>
- /// <param name="para">参数</param>
- /// <returns>影响的行数</returns>
- public static int ExecuteQuery(CommandType commandType, string commandText, string constr, params SqlParameter[] para)
- {
- using (SqlConnection conn = new SqlConnection(constr))
- {
- return ExecuteNonQuery(conn, commandType, commandText, para);
- }
- }
- /// <summary>
- /// 执行SQL语句,返回影响行数并返回主键ID
- /// </summary>
- /// <param name="commandType">操作类型</param>
- /// <param name="commandText">SQL语句</param>
- /// <param name="constr">数据库连接</param>
- /// <param name="id">抛出的主键ID</param>
- /// <param name="para">参数</param>
- /// <returns>影响的行数</returns>
- public static int ExecuteQuery(CommandType commandType, string commandText, string constr, out int id, params SqlParameter[] para)
- {
- id = 0;
- int result = -1;
- using (SqlConnection conn = new SqlConnection(constr))
- {
- SqlCommand cmd = new SqlCommand();
- PrepareCommand(cmd, conn, (SqlTransaction)null, commandType, commandText, para);
- try
- {
- result = cmd.ExecuteNonQuery();
- id = int.Parse(cmd.Parameters["@" + ParameterDirection.ReturnValue.ToString()].Value.ToString());
- cmd.Parameters.Clear();
- }
- catch (Exception e)
- {
- throw e;
- }
- finally
- {
- CloseConnection(conn);
- }
- }
- return result;
- }
- /// <summary>
- /// 执行SQL语句,返回影响的记录数
- /// </summary>
- /// <param name="connection">SqlConnection对象</param>
- /// <param name="trans">SqlTransaction对象</param>
- /// <param name="SQLString">SQL语句</param>
- /// <returns>影响的记录数</returns>
- public static int ExecuteSql(SqlConnection connection, SqlTransaction trans, string SQLString, params SqlParameter[] cmdParms)
- {
- using (SqlCommand cmd = new SqlCommand())
- {
- int rows = 0;
- try
- {
- PrepareCommand(cmd, connection, trans, CommandType.Text, SQLString, cmdParms);
- rows = cmd.ExecuteNonQuery();
- cmd.Parameters.Clear();
- }
- catch (System.Data.SqlClient.SqlException e)
- {
- trans.Rollback();
- throw e;
- }
- finally
- {
- CloseConnection(connection);
- }
- return rows;
- }
- }
- #endregion
- #region 获取数据集,含分页
- /// <summary>
- /// 对应object数组到SqlParameter数组
- /// </summary>
- /// <param name="commandParameters">SqlParameter参数数组</param>
- /// <param name="parameterValues">object参数数组</param>
- private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
- {
- if ((commandParameters != null) && (parameterValues != null))
- {
- if (commandParameters.Length != parameterValues.Length)
- {
- throw new ArgumentException("Parameter count does not match Parameter Value count.");
- }
- int index = 0;
- int length = commandParameters.Length;
- while (index < length)
- {
- if (parameterValues[index] is IDbDataParameter)
- {
- IDbDataParameter parameter = (IDbDataParameter)parameterValues[index];
- if (parameter.Value == null)
- {
- commandParameters[index].Value = DBNull.Value;
- }
- else
- {
- commandParameters[index].Value = parameter.Value;
- }
- }
- else if (parameterValues[index] == null)
- {
- commandParameters[index].Value = DBNull.Value;
- }
- else
- {
- commandParameters[index].Value = parameterValues[index];
- }
- index++;
- }
- }
- }
- /// <summary>
- /// 获取数据集 存储过程使用
- /// </summary>
- /// <param name="connectionString">数据库连接字符串</param>
- /// <param name="commandType">SqlCommand命令类型</param>
- /// <param name="commandText">命令内容</param>
- /// <param name="commandParameters">命令用到参数集合</param>
- /// <returns>数据集</returns>
- public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params object[] commandParameters)
- {
- SqlCommand cmd = new SqlCommand();
- SqlConnection conn = new SqlConnection(connectionString);
- SqlParameter[] spParameterSet = SqlHelperParameterCache.GetSpParameterSet(connectionString, commandText);
- AssignParameterValues(spParameterSet, commandParameters);
- PrepareCommand(cmd, conn, null, commandType, commandText, spParameterSet);
- using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
- {
- DataSet dataSet = new DataSet();
- try
- {
- adapter.Fill(dataSet);
- cmd.Parameters.Clear();
- }
- catch (Exception e)
- {
- throw e;
- }
- finally
- {
- CloseConnection(conn);
- }
- return dataSet;
- }
- }
- /// <summary>
- /// 执行查询语句,返回DataSet
- /// </summary>
- /// <param name="connection">SqlConnection对象</param>
- /// <param name="SQLString">查询语句</param>
- /// <returns>DataSet</returns>
- public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
- {
- SqlConnection conn = new SqlConnection(connectionString);
- SqlCommand cmd = new SqlCommand();
- PrepareCommand(cmd, conn, (SqlTransaction)null, commandType, commandText, commandParameters);
- using (SqlDataAdapter da = new SqlDataAdapter(cmd))
- {
- DataSet ds = new DataSet();
- try
- {
- da.Fill(ds);
- cmd.Parameters.Clear();
- }
- catch
- {
- throw;
- }
- finally
- {
- CloseConnection(conn);
- }
- return ds;
- }
- }
- /// <summary>
- /// 执行查询语句,返回DataSet
- /// </summary>
- /// <param name="SQLString">查询语句</param>
- /// <returns>DataSet</returns>
- public static DataSet Query(string SQLString)
- {
- using (SqlConnection connection = new SqlConnection(constr))
- {
- DataSet ds = new DataSet();
- SqlCommand cmd = new SqlCommand();
- PrepareCommand(cmd, connection, null, CommandType.Text, SQLString, null);
- try
- {
- SqlDataAdapter command = new SqlDataAdapter(cmd);
- command.Fill(ds, "ds");
- command.Dispose();
- }
- catch (System.Data.SqlClient.SqlException ex)
- {
- throw new Exception(ex.Message);
- }
- finally
- {
- CloseConnection(connection);
- }
- return ds;
- }
- }
- /// <summary>
- /// 执行查询语句,返回DataSet
- /// </summary>
- /// <param name="SQLString">查询语句</param>
- /// <returns>DataSet</returns>
- public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
- {
- using (SqlConnection connection = new SqlConnection(constr))
- {
- SqlCommand cmd = new SqlCommand();
- PrepareCommand(cmd, connection, null, CommandType.Text, SQLString, cmdParms);
- using (SqlDataAdapter da = new SqlDataAdapter(cmd))
- {
- DataSet ds = new DataSet();
- try
- {
- da.Fill(ds, "ds");
- cmd.Parameters.Clear();
- }
- catch (System.Data.SqlClient.SqlException ex)
- {
- throw new Exception(ex.Message);
- }
- finally
- {
- CloseConnection(connection);
- }
- return ds;
- }
- }
- }
- /// <summary>
- /// 执行查询语句,返回DataSet
- /// </summary>
- /// <param name="connection">SqlConnection对象</param>
- /// <param name="trans">SqlTransaction事务</param>
- /// <param name="SQLString">查询语句</param>
- /// <returns>DataSet</returns>
- public static DataSet Query(SqlConnection connection, SqlTransaction trans, string SQLString, params SqlParameter[] cmdParms)
- {
- SqlCommand cmd = new SqlCommand();
- PrepareCommand(cmd, connection, trans, CommandType.Text, SQLString, cmdParms);
- using (SqlDataAdapter da = new SqlDataAdapter(cmd))
- {
- DataSet ds = new DataSet();
- try
- {
- da.Fill(ds, "ds");
- cmd.Parameters.Clear();
- }
- catch (System.Data.SqlClient.SqlException ex)
- {
- trans.Rollback();
- throw new Exception(ex.Message);
- }
- finally
- {
- CloseConnection(connection);
- }
- return ds;
- }
- }
- /// <summary>
- /// Pagination 获取分页数据
- /// </summary>
- /// <param name="item">分页实体类,包含分页存储过程所用到的各个参数</param>
- /// <param name="connectionString">数据库连接字符串</param>
- /// <param name="ProName">存储过程名称</param>
- /// <returns>某一页所有记录</returns>
- public static DataSet GetPageContent(Pagination item, string connectionString, string ProName)
- {
- object[] commandParameters = new object[] { item.Tables, item.PrimaryKey, item.Sort, item.CurrentPage, item.PageSize, item.Fields, item.Filter, item.Group };
- return ExecuteDataset(connectionString, CommandType.StoredProcedure, ProName, commandParameters);
- }
- /// <summary>
- /// PaginationByRowNumber获取分页数据
- /// </summary>
- /// <param name="item">分页实体</param>
- /// <param name="constr">数据库链接</param>
- /// <param name="ProName">过程名称</param>
- /// <returns>数据集</returns>
- public static DataSet GetPageContent(PaginationByRowNumber item, string constr, string ProName)
- {
- object[] commandParameter = new object[] { item.Table, item.Sort, item.CurrentPage, item.PageSize, item.Fields, item.Filter };
- return ExecuteDataset(constr, CommandType.StoredProcedure, ProName, commandParameter);
- }
- /// <summary>
- /// 获取分页数据
- /// </summary>
- /// <param name="pt">分页实体类,包含分页存储过程所用到的各个参数</param>
- /// <param name="ProName">存储过程名称</param>
- /// <param name="constr">数据库连接字符串</param>
- /// <returns>某一页所有记录</returns>
- public static DataSet GetItems(Pagination pt, string ProName, string constr)
- {
- return GetPageContent(pt, constr, ProName);
- }
- /// <summary>
- /// 获取数据集
- /// </summary>
- /// <param name="commandType">Sql命令类型</param>
- /// <param name="commandText">Sql命令内容</param>
- /// <param name="constr">数据库连接字符串</param>
- /// <returns>数据集</returns>
- public static DataSet SqlTextDataset(CommandType commandType, string commandText, string constr)
- {
- return ExecuteDataset(constr, commandType, commandText, null);
- }
- /// <summary>
- /// 获取数据集
- /// </summary>
- /// <param name="commandType">数据类型</param>
- /// <param name="commandText">SQL语句或过程</param>
- /// <param name="constr">数据库连接</param>
- /// <param name="cmdParms">参数</param>
- /// <returns>DataSet</returns>
- public static DataSet SqlTextDataset(CommandType commandType, string commandText, string constr, params SqlParameter[] cmdParms)
- {
- return ExecuteDataset(constr, commandType, commandText, cmdParms);
- }
- /// <summary>
- /// 事务处理,执行一条计算查询结果语句,返回查询结果(object)。
- /// </summary>
- /// <param name="connection">SqlConnection对象</param>
- /// <param name="trans">SqlTransaction事务</param>
- /// <param name="SQLString">计算查询结果语句</param>
- /// <returns>查询结果(object)</returns>
- public static object GetSingle(SqlConnection connection, SqlTransaction trans, CommandType commandType, string SQLString, params SqlParameter[] cmdParms)
- {
- using (SqlCommand cmd = new SqlCommand())
- {
- try
- {
- PrepareCommand(cmd, connection, trans, commandType, SQLString, cmdParms);
- object obj = cmd.ExecuteScalar();
- cmd.Parameters.Clear();
- if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
- {
- return null;
- }
- else
- {
- return obj;
- }
- }
- catch (Exception e)
- {
- trans.Rollback();
- throw e;
- }
- finally
- {
- connection.Close();
- }
- }
- }
- #endregion
- #region 操作帮助方法
- /// <summary>
- /// SqlCommand参数初始化
- /// </summary>
- /// <param name="cmd">Sql命令</param>
- /// <param name="conn">数据库连接对象</param>
- /// <param name="trans">事务对象</param>
- /// <param name="cmdType">Sql命令类型</param>
- /// <param name="cmdText">Sql命令内容</param>
- /// <param name="cmdParms">Sql命令参数集合</param>
- private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
- {
- if (conn.State != ConnectionState.Open)
- {
- conn.Open();
- }
- cmd.Connection = conn;
- cmd.CommandText = cmdText;
- if (trans != null)
- {
- cmd.Transaction = trans;
- }
- cmd.CommandType = cmdType;
- if (cmdParms != null)
- {
- foreach (SqlParameter parameter in cmdParms)
- {
- if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
- (parameter.Value == null))
- {
- parameter.Value = DBNull.Value;
- }
- cmd.Parameters.Add(parameter);
- }
- }
- }
- /// <summary>
- /// 关闭数据库连接 释放资源
- /// </summary>
- /// <param name="connection">连接对象</param>
- public static void CloseConnection(SqlConnection connection)
- {
- if (connection.State != ConnectionState.Closed)
- {
- connection.Dispose();
- connection.Close();
- }
- }
- #endregion
- #region 建立一个参数 包括Dictionary和T
- /// <summary>
- /// OOP根据实体类和所属某个属性返回一个SQL参数
- /// </summary>
- /// <typeparam name="T">数据实体类</typeparam>
- /// <param name="p">属性</param>
- /// <param name="t">数据实体对象</param>
- /// <returns>一个SQL参数,已经初始化</returns>
- private static SqlParameter createParam(PropertyInfo p, object v)
- {
- SqlParameter sp = null;
- var propertyType = Nullable.GetUnderlyingType(p.PropertyType) ?? p.PropertyType;//得到属性定义类型
- string t_p = propertyType.ToString().ToLower();//转化为小写
- t_p = t_p.Substring(t_p.IndexOf('.') + 1, t_p.Length - t_p.IndexOf('.') - 1);
- // int->Int32 decimal->Decimal float->Single double->Double bool->Boolean datetime->DateTime string->String char->Char
- switch (t_p)
- {
- case "byte":
- sp = new SqlParameter("@" + p.Name, SqlDbType.TinyInt);
- sp.Value = int.Parse(v.ToString());
- break;
- case "int16":
- sp = new SqlParameter("@" + p.Name, SqlDbType.SmallInt);
- sp.Value = int.Parse(v.ToString());
- break;
- case "int32":
- sp = new SqlParameter("@" + p.Name, SqlDbType.Int);
- sp.Value = int.Parse(v.ToString());
- break;
- case "decimal":
- sp = new SqlParameter("@" + p.Name, SqlDbType.Decimal);
- sp.Value = decimal.Parse(v.ToString());
- break;
- case "single":
- sp = new SqlParameter("@" + p.Name, SqlDbType.Float);
- sp.Value = float.Parse(v.ToString());
- break;
- case "double":
- sp = new SqlParameter("@" + p.Name, SqlDbType.Decimal);
- sp.Value = decimal.Parse(v.ToString());
- break;
- case "boolean":
- sp = new SqlParameter("@" + p.Name, SqlDbType.Bit);
- if (v.ToString().ToLower() == "true")
- {
- sp.Value = 1;
- }
- else if (v.ToString().ToLower() == "false")
- {
- sp.Value = 0;
- }
- break;
- case "datetime":
- sp = new SqlParameter("@" + p.Name, SqlDbType.DateTime);
- sp.Value = (DateTime)v;
- break;
- case "string":
- //程序测试时注意数据库表中字段是双字节和单字节可变类型情况
- if (System.Text.RegularExpressions.Regex.IsMatch(v.ToString(), @"[^\x00-\xff]+"))
- {
- string name = p.Name.ToLower();
- if (name.IndexOf("remark") >= 0 || name.IndexOf("content") >= 0 || name.IndexOf("description") >= 0)
- {
- sp = new SqlParameter("@" + p.Name, SqlDbType.NVarChar, 2000);
- }
- else
- {
- sp = new SqlParameter("@" + p.Name, SqlDbType.VarChar, 50);
- }
- }
- else
- {
- sp = new SqlParameter("@" + p.Name, SqlDbType.VarChar, 50);
- }
- sp.Value = v.ToString();
- break;
- case "char":
- sp = new SqlParameter("@" + p.Name, SqlDbType.Char, 1);
- sp.Value = v.ToString();
- break;
- }
- return sp;
- }
- /// <summary>
- /// 利用Key-Value创建一个参数
- /// </summary>
- /// <param name="current">键值对</param>
- /// <returns>SQL参数,已经初始化</returns>
- public static SqlParameter createParam(string key, object obj)
- {
- SqlParameter sp = null;
- string t_p = obj.GetType().ToString().ToLower();//转化为小写
- t_p = t_p.Substring(t_p.IndexOf('.') + 1, t_p.Length - t_p.IndexOf('.') - 1);
- // int->Int32 decimal->Decimal float->Single double->Double bool->Boolean datetime->DateTime string->String char->Char
- switch (t_p)
- {
- case "byte":
- sp = new SqlParameter("@" + key, SqlDbType.TinyInt);
- sp.Value = int.Parse(obj.ToString());
- break;
- case "int16":
- sp = new SqlParameter("@" + key, SqlDbType.SmallInt);
- sp.Value = int.Parse(obj.ToString());
- break;
- case "int32":
- sp = new SqlParameter("@" + key, SqlDbType.Int);
- sp.Value = int.Parse(obj.ToString());
- break;
- case "decimal":
- sp = new SqlParameter("@" + key, SqlDbType.Decimal);
- sp.Value = decimal.Parse(obj.ToString());
- break;
- case "single":
- sp = new SqlParameter("@" + key, SqlDbType.Float);
- sp.Value = float.Parse(obj.ToString());
- break;
- case "double":
- sp = new SqlParameter("@" + key, SqlDbType.Decimal);
- sp.Value = decimal.Parse(obj.ToString());
- break;
- case "boolean":
- sp = new SqlParameter("@" + key, SqlDbType.Bit);
- if (obj.ToString().ToLower() == "true")
- {
- sp.Value = 1;
- }
- else if (obj.ToString().ToLower() == "false")
- {
- sp.Value = 0;
- }
- break;
- case "datetime":
- sp = new SqlParameter("@" + key, SqlDbType.DateTime);
- sp.Value = (DateTime)obj;
- break;
- case "string":
- if (System.Text.RegularExpressions.Regex.IsMatch(obj.ToString(), @"[^\x00-\xff]+"))
- {
- string name = key.ToLower();
- if (name.IndexOf("remark") >= 0 || name.IndexOf("content") >= 0 || name.IndexOf("description") >= 0)
- {
- sp = new SqlParameter("@" + key, SqlDbType.NVarChar, 2000);
- }
- else
- {
- sp = new SqlParameter("@" + key, SqlDbType.VarChar, 50);
- }
- }
- else
- {
- sp = new SqlParameter("@" + key, SqlDbType.VarChar, 50);
- }
- sp.Value = obj.ToString();
- break;
- case "char":
- sp = new SqlParameter("@" + key, SqlDbType.Char, 1);
- sp.Value = obj.ToString();
- break;
- }
- return sp;
- }
- #endregion
- }
- /// <summary>
- /// 构造SQL命令和对应参数
- /// </summary>
- public class CommandInfo
- {
- public string CommandText;
- public System.Data.Common.DbParameter[] Parameters;
- public CommandInfo(string sqlText, SqlParameter[] para)
- {
- this.CommandText = sqlText;
- this.Parameters = para;
- }
- }
- #region 分页类实体,借助存储过程实现
- public class Pagination
- {
- public int CurrentPage
- {
- get;
- set;
- }
- public string Fields
- {
- get;
- set;
- }
- public string Filter
- {
- get;
- set;
- }
- public string Group
- {
- get;
- set;
- }
- public int PageSize
- {
- get;
- set;
- }
- public string PrimaryKey
- {
- get;
- set;
- }
- public string Sort
- {
- get;
- set;
- }
- public string Tables
- {
- get;
- set;
- }
- }
- /// <summary>
- /// RowNumber分页
- /// </summary>
- public class PaginationByRowNumber
- {
- /// <summary>
- /// 表名
- /// </summary>
- public string Table { get; set; }
- /// <summary>
- /// 排序 需要指明顺序 asc/desc
- /// </summary>
- public string Sort { get; set; }
- /// <summary>
- /// 当前页
- /// </summary>
- public int CurrentPage { get; set; }
- /// <summary>
- /// 行数
- /// </summary>
- public int PageSize { get; set; }
- /// <summary>
- /// 字段
- /// </summary>
- public string Fields { get; set; }
- /// <summary>
- /// 条件
- /// </summary>
- public string Filter { get; set; }
- }
- #endregion
- #region 使用存储过程时缓存参数
- public sealed class SqlHelperParameterCache
- {
- private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
- private SqlHelperParameterCache()
- {
- }
- public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters)
- {
- if ((connectionString == null) || (connectionString.Length == 0))
- {
- throw new ArgumentNullException("connectionString");
- }
- if ((commandText == null) || (commandText.Length == 0))
- {
- throw new ArgumentNullException("commandText");
- }
- string str = connectionString + ":" + commandText;
- paramCache[str] = commandParameters;
- }
- private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
- {
- SqlParameter[] parameterArray = new SqlParameter[originalParameters.Length];
- int index = 0;
- int length = originalParameters.Length;
- while (index < length)
- {
- parameterArray[index] = (SqlParameter)((ICloneable)originalParameters[index]).Clone();
- index++;
- }
- return parameterArray;
- }
- private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
- {
- if (connection == null)
- {
- throw new ArgumentNullException("connection");
- }
- if ((spName == null) || (spName.Length == 0))
- {
- throw new ArgumentNullException("spName");
- }
- SqlCommand command = new SqlCommand(spName, connection);
- command.CommandType = CommandType.StoredProcedure;
- connection.Open();
- SqlCommandBuilder.DeriveParameters(command);
- connection.Close();
- if (!includeReturnValueParameter)
- {
- command.Parameters.RemoveAt(0);
- }
- SqlParameter[] array = new SqlParameter[command.Parameters.Count];
- command.Parameters.CopyTo(array, 0);
- foreach (SqlParameter parameter in array)
- {
- parameter.Value = DBNull.Value;
- }
- return array;
- }
- public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
- {
- if ((connectionString == null) || (connectionString.Length == 0))
- {
- throw new ArgumentNullException("connectionString");
- }
- if ((commandText == null) || (commandText.Length == 0))
- {
- throw new ArgumentNullException("commandText");
- }
- string str = connectionString + ":" + commandText;
- SqlParameter[] originalParameters = paramCache[str] as SqlParameter[];
- if (originalParameters == null)
- {
- return null;
- }
- return CloneParameters(originalParameters);
- }
- internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName)
- {
- return GetSpParameterSet(connection, spName, false);
- }
- public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
- {
- return GetSpParameterSet(connectionString, spName, false);
- }
- internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
- {
- if (connection == null)
- {
- throw new ArgumentNullException("connection");
- }
- using (SqlConnection connection2 = (SqlConnection)((ICloneable)connection).Clone())
- {
- return GetSpParameterSetInternal(connection2, spName, includeReturnValueParameter);
- }
- }
- public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
- {
- if ((connectionString == null) || (connectionString.Length == 0))
- {
- throw new ArgumentNullException("connectionString");
- }
- if ((spName == null) || (spName.Length == 0))
- {
- throw new ArgumentNullException("spName");
- }
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
- }
- }
- private static SqlParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter)
- {
- if (connection == null)
- {
- throw new ArgumentNullException("connection");
- }
- if ((spName == null) || (spName.Length == 0))
- {
- throw new ArgumentNullException("spName");
- }
- string str = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");
- SqlParameter[] originalParameters = paramCache[str] as SqlParameter[];
- if (originalParameters == null)
- {
- SqlParameter[] parameterArray2 = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
- paramCache[str] = parameterArray2;
- originalParameters = parameterArray2;
- }
- return CloneParameters(originalParameters);
- }
- }
- #endregion
- /// <summary>
- /// 创建SqlParameter
- /// </summary>
- public class CP
- {
- public class ColumnInfo
- {
- public string ColName { get; set; }
- public SqlDbType ColType { get; set; }
- public int ColLength { get; set; }
- public bool IsPrimary { get; set; }
- public string Description { get; set; }
- }
- /// <summary>
- /// 读取用户表及每个表字段信息保存到字典对象,加入缓存
- /// </summary>
- /// <param name="specifyTable">指定表名,如果为空,则查询所有表</param>
- /// <param name="connStr">数据库连接串</param>
- /// <returns></returns>
- public static List<ColumnInfo> TC(string specifyTable, string connStr)
- {
- if (string.IsNullOrEmpty(specifyTable))
- {
- return null;
- }
- var vl = HttpRuntime.Cache.Get("utable_" + specifyTable);
- if (vl == null)
- {
- List<ColumnInfo> p = new List<ColumnInfo>();
- using (SqlConnection conn = new SqlConnection(connStr))
- {
- StringBuilder sb = new StringBuilder(1000);
- sb.Append("SELECT a.name,(case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 then '1' else '0' end) isprimary,b.name [type],COLUMNPROPERTY(a.id,a.name,'PRECISION') as [length],isnull(g.value,'') as [description] FROM syscolumns a ");
- sb.Append("left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'");
- sb.Append("left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id AND a.colid = g.minor_id where d.name =@tname order by a.id,a.colorder");
- conn.Open();
- SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
- cmd.Parameters.Add(new SqlParameter("@tname", SqlDbType.VarChar, 30));
- cmd.Parameters[0].Value = specifyTable;
- SqlDataAdapter sda = new SqlDataAdapter(cmd);
- DataSet ds = new DataSet();
- sda.Fill(ds);
- DataTable dt = ds.Tables[0];
- int count = dt.Rows.Count;
- if (count > 0)
- {
- for (int i = 0; i < count; i++)
- {
- ColumnInfo item = new ColumnInfo() { ColName = dt.Rows[i]["name"].ToString(), ColLength = int.Parse(dt.Rows[i]["length"].ToString()), ColType = StringToSqlType(dt.Rows[i]["type"].ToString()), IsPrimary = (dt.Rows[i]["isprimary"].ToString() == "1" ? true : false), Description = dt.Rows[i]["description"].ToString() };
- p.Add(item);
- }
- HttpRuntime.Cache.Insert("utable_" + specifyTable, p, null, Cache.NoAbsoluteExpiration, new TimeSpan(0, 30, 0));
- }
- }
- return p;
- }
- else
- {
- return (List<ColumnInfo>)HttpRuntime.Cache["utable_" + specifyTable];
- }
- }
- /// <summary>
- /// 读取用户表及每个表字段信息保存到字典对象,加入缓存 只针对视图
- /// </summary>
- /// <param name="specifyTable">指定表名,如果为空,则查询所有表</param>
- /// <param name="connStr">数据库连接串</param>
- /// <returns></returns>
- public static List<ColumnInfo> TCV(string specifyTable, string connStr)
- {
- if (string.IsNullOrEmpty(specifyTable))
- {
- return null;
- }
- if (HttpRuntime.Cache["utable_" + specifyTable] == null)
- {
- List<ColumnInfo> p = new List<ColumnInfo>();
- using (SqlConnection conn = new SqlConnection(connStr))
- {
- StringBuilder sb = new StringBuilder(1000);
- sb.Append("select c.name ,t.name as type ,COLUMNPROPERTY(c.id,c.name,'PRECISION') as length ");
- sb.Append("from syscolumns c inner join systypes t on c.xusertype=t.xusertype ");
- sb.Append("where objectproperty(c.id,'IsView')=1 and c.id=object_id(@tname) ");
- conn.Open();
- SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
- cmd.Parameters.Add(new SqlParameter("@tname", SqlDbType.VarChar, 30));
- cmd.Parameters[0].Value = specifyTable;
- SqlDataAdapter sda = new SqlDataAdapter(cmd);
- DataSet ds = new DataSet();
- sda.Fill(ds);
- DataTable dt = ds.Tables[0];
- int count = dt.Rows.Count;
- if (count > 0)
- {
- for (int i = 0; i < count; i++)
- {
- ColumnInfo item = new ColumnInfo() { ColName = dt.Rows[i]["name"].ToString(), ColLength = int.Parse(dt.Rows[i]["length"].ToString()), ColType = StringToSqlType(dt.Rows[i]["type"].ToString()), IsPrimary = false, Description = "" };
- p.Add(item);
- }
- HttpRuntime.Cache.Insert("utable_" + specifyTable, p, null, Cache.NoAbsoluteExpiration, new TimeSpan(0, 30, 0));
- }
- }
- return p;
- }
- else
- {
- return (List<ColumnInfo>)HttpRuntime.Cache["utable_" + specifyTable];
- }
- }
- //SqlServer数据库类型转换方法
- public static SqlDbType StringToSqlType(string String)
- {
- SqlDbType dbType = SqlDbType.Variant;//默认为Object
- switch (String)
- {
- case "int":
- dbType = SqlDbType.Int;
- break;
- case "varchar":
- dbType = SqlDbType.VarChar;
- break;
- case "bit":
- dbType = SqlDbType.Bit;
- break;
- case "datetime":
- dbType = SqlDbType.DateTime;
- break;
- case "decimal":
- dbType = SqlDbType.Decimal;
- break;
- case "float":
- dbType = SqlDbType.Float;
- break;
- case "image":
- dbType = SqlDbType.Image;
- break;
- case "money":
- dbType = SqlDbType.Money;
- break;
- case "ntext":
- dbType = SqlDbType.NText;
- break;
- case "nvarchar":
- dbType = SqlDbType.NVarChar;
- break;
- case "smalldatetime":
- dbType = SqlDbType.SmallDateTime;
- break;
- case "smallint":
- dbType = SqlDbType.SmallInt;
- break;
- case "text":
- dbType = SqlDbType.Text;
- break;
- case "bigint":
- dbType = SqlDbType.BigInt;
- break;
- case "binary":
- dbType = SqlDbType.Binary;
- break;
- case "char":
- dbType = SqlDbType.Char;
- break;
- case "nchar":
- dbType = SqlDbType.NChar;
- break;
- case "numeric":
- dbType = SqlDbType.Decimal;
- break;
- case "real":
- dbType = SqlDbType.Real;
- break;
- case "smallmoney":
- dbType = SqlDbType.SmallMoney;
- break;
- case "sql_variant":
- dbType = SqlDbType.Variant;
- break;
- case "timestamp":
- dbType = SqlDbType.Timestamp;
- break;
- case "tinyint":
- dbType = SqlDbType.TinyInt;
- break;
- case "uniqueidentifier":
- dbType = SqlDbType.UniqueIdentifier;
- break;
- case "varbinary":
- dbType = SqlDbType.VarBinary;
- break;
- case "xml":
- dbType = SqlDbType.Xml;
- break;
- }
- return dbType;
- }
- #region 建立一个参数
- /// <summary>
- /// 根据表列信息集合和表对应实体类所属某个属性返回一个SQL参数
- /// </summary>
- /// <typeparam name="item">表列信息集合</typeparam>
- /// <param name="property">属性</param>
- /// <param name="v">属性对应值</param>
- /// <param name="pd">参数方向</param>
- /// <returns>一个SQL参数,已经初始化</returns>
- public static SqlParameter cPa(ColumnInfo item, object v, ParameterDirection pd)
- {
- if (item == null)
- {
- return null;
- }
- SqlParameter sp = null;
- if (item != null)
- {
- sp = new SqlParameter("@" + item.ColName, item.ColType, item.ColLength);
- sp.Direction = pd;
- sp.Value = v;
- }
- return sp;
- }
- /// <summary>
- /// 根据表列信息集合和表对应实体类所属某个属性返回一个SQL参数
- /// </summary>
- /// <typeparam name="item">表列信息集合</typeparam>
- /// <param name="property">属性</param>
- /// <param name="v">属性对应值</param>
- /// <returns>一个SQL参数,已经初始化</returns>
- public static SqlParameter cPa(ColumnInfo item, object v)
- {
- if (item == null)
- {
- return null;
- }
- SqlParameter sp = null;
- if (item != null)
- {
- sp = new SqlParameter("@" + item.ColName, item.ColType, item.ColLength);
- sp.Direction = ParameterDirection.Input;
- sp.Value = v;
- }
- return sp;
- }
- #endregion
- /// <summary>
- /// Author:付裕
- /// Date: 2014-09-19
- /// Desc: DataTable与泛型转换类
- /// </summary>
- public class WorkCommon
- {
- /// <summary>
- /// DataTable Convert To List<T>
- /// </summary>
- /// <typeparam name="T">实体</typeparam>
- /// <param name="dt">数据集合</param>
- /// <returns>泛型集合</returns>
- public static List<T> ConvertTo<T>(DataTable dt) where T : new()
- {
- if (dt == null) return null;
- if (dt.Rows.Count <= 0) return null;
- List<T> list = new List<T>();
- try
- {
- List<string> columnsName = new List<string>();
- foreach (DataColumn dataColumn in dt.Columns)
- {
- columnsName.Add(dataColumn.ColumnName);//得到所有的表头
- }
- list = dt.AsEnumerable().ToList().ConvertAll<T>(row => getObject<T>(row, columnsName)); //转换
- return list;
- }
- catch
- {
- return null;
- }
- }
- /// <summary>
- /// 转换函数
- /// </summary>
- /// <typeparam name="T"></typeparam>
- /// <param name="row"></param>
- /// <param name="columnsName"></param>
- /// <returns></returns>
- private static T getObject<T>(DataRow row, List<string> columnsName) where T : new()
- {
- T obj = new T();
- try
- {
- string columnname = "";
- string value = "";
- PropertyInfo[] Properties = typeof(T).GetProperties();
- foreach (PropertyInfo objProperty in Properties) //遍历T的属性
- {
- columnname = columnsName.Find(name => name.ToLower() == objProperty.Name.ToLower()); //寻找可以匹配的表头名称
- if (!string.IsNullOrEmpty(columnname))
- {
- value = row[columnname].ToString();
- if (!string.IsNullOrEmpty(value))
- {
- if (Nullable.GetUnderlyingType(objProperty.PropertyType) != null) //存在匹配的表头
- {
- value = row[columnname].ToString().Replace("$", "").Replace(",", ""); //从dataRow中提取数据
- objProperty.SetValue(obj, Convert.ChangeType(value, Type.GetType(Nullable.GetUnderlyingType(objProperty.PropertyType).ToString())), null); //赋值操作
- }
- else
- {
- value = row[columnname].ToString().Replace("%", ""); //存在匹配的表头
- objProperty.SetValue(obj, Convert.ChangeType(value, Type.GetType(objProperty.PropertyType.ToString())), null);//赋值操作
- }
- }
- }
- }
- return obj;
- }
- catch
- {
- return obj;
- }
- }
- /// <summary>
- /// 将泛型集合类转换成DataTable
- /// </summary>
- /// <typeparam name="T">集合项类型</typeparam>
- /// <param name="list">集合</param>
- /// <param name="propertyName">需要返回的列的列名</param>
- /// <returns>数据集(表)</returns>
- public static DataTable ListToDataTable<T>(IList<T> list, params string[] propertyName)
- {
- List<string> propertyNameList = new List<string>();
- if (propertyName != null)
- propertyNameList.AddRange(propertyName);
- DataTable result = new DataTable();
- if (list != null && list.Count > 0)
- {
- PropertyInfo[] propertys = list[0].GetType().GetProperties();
- foreach (PropertyInfo pi in propertys)
- {
- if (propertyNameList.Count == 0)
- {
- result.Columns.Add(pi.Name, pi.PropertyType);
- }
- else
- {
- if (propertyNameList.Contains(pi.Name))
- result.Columns.Add(pi.Name, pi.PropertyType);
- }
- }
- for (int i = 0; i < list.Count; i++)
- {
- ArrayList tempList = new ArrayList();
- foreach (PropertyInfo pi in propertys)
- {
- if (propertyNameList.Count == 0)
- {
- object obj = pi.GetValue(list[i], null);
- tempList.Add(obj);
- }
- else
- {
- if (propertyNameList.Contains(pi.Name))
- {
- object obj = pi.GetValue(list[i], null);
- tempList.Add(obj);
- }
- }
- }
- object[] array = tempList.ToArray();
- result.LoadDataRow(array, true);
- }
- }
- return result;
- }
- }
- }
|