oledbhelper.cs

会员管理系统

		public static string GetInsertSql(DataRow row,string tableName)
		{
			int columnCount=row.Table.Columns.Count;			
			StringBuilder insString=new StringBuilder("insert into  "+ tableName + "  values (  ");				
			for(int j=0;j<columnCount;j++)
			{
				switch(row[j].GetType().ToString())
				{
					case	"System.String":
							insString.Append("'"+row[j].ToString()+"'").Append(",");
							break;
					case	"System.DateTime":
							insString.Append("'"+row[j].ToString()+"'").Append(",");
							break;
					default:
						if (row[j] == DBNull.Value)
						{
							insString.Append("null").Append(",");
						}
						else
						{
							insString.Append(row[j].ToString()).Append(",");
						}
						break;
				}				
			}	
			insString.Remove(insString.Length-1,1).Append(")");
			return insString.ToString();				
		}
		
		
		
		#endregion

		#region 得到需要执行update操作的Sql语句（集）

		/// <summary>
		/// 得到更新数据库记录的sql语句集，需要保证内存表的字段与数据库表的字段拼写一致。
		/// </summary>
		/// <param name="dt">内存表</param>
		/// <param name="tableName">需要更新的数据库表名。</param>
		/// <param name="pkName">表示主键的数组。</param>
		/// <returns>成功返回sql语句，否则返回null。</returns>
		public static string[] GetUpdateSql(DataTable dt,string tableName ,string[] pkName)
		{
			if(dt==null)
			{
				return null;
			}

			int rowCount=dt.Rows.Count;
			if(rowCount < 1)
			{
				return null;
			}

			string[] sqlString=new string[rowCount];
			DataRow row;
			
			for(int i= 0 ;i< rowCount;i++)													
			{
				row=dt.Rows[i];
				string updateString=GetUpdateSql(row,tableName,pkName);
				sqlString.SetValue(updateString,i);
			}
			return sqlString;
		}


		/// <summary>
		/// 得到更新数据库记录的sql语句，需要保证内存表的字段与数据库表的字段拼写一致。
		/// </summary>
		/// <param name="row">内存表中的一条记录。</param>
		/// <param name="tableName">需要更新的数据库表名。</param>
		/// <param name="pkName">表示主键的数组。</param>
		/// <returns>成功返回sql语句，否则返回null。</returns>
		public static string GetUpdateSql(DataRow row,string tableName ,string[] pkName)
		{
			if(pkName.Length<1)
				return null;

			StringBuilder updateString=new StringBuilder("update "+tableName+"   set   ");			
			StringBuilder whereString=new StringBuilder("	  where		");
			int columnCount=row.Table.Columns.Count;

			for(int i=0;i<columnCount;i++)
			{
				string columnName=row.Table.Columns[i].ColumnName;
				string columnValue=row[i].ToString();

				switch(row[i].GetType().ToString())
				{
					case	"System.String":
						columnValue="'"+row[i].ToString()+"'";
						break;
					case	"System.DateTime":
						columnValue="'"+row[i].ToString()+"'";
						break;
					default:
						columnValue=row[i].ToString();
						break;
				}			

				bool isPK=true;
				for(int k=0;k<pkName.Length;k++)
				{
					if(pkName[k].CompareTo(columnName)!=-1)
					{
						isPK=true;
						whereString.Append( pkName[k]  + "	=	" +columnValue+"	and");
						break;
					}
					isPK=false;
				}

				if(!isPK)
				{
					updateString.Append( columnName +" = "+columnValue).Append(",");
				}						
			}
			
			updateString.Remove(updateString.Length-1,1).Append(whereString.Remove(whereString.Length-3,3).ToString());						
			return updateString.ToString();
		}

		
		/// <summary>
		/// 得到更新数据库记录的sql语句集，需要保证内存表的字段与数据库表的字段拼写一致。
		/// </summary>
		/// <param name="dt">内存表</param>
		/// <param name="tableName">需要更新的数据库表名。</param>
		/// <param name="pkName">主键</param>
		/// <returns>成功返回sql语句，否则返回null。</returns>
		public static string[] GetUpdateSql(DataTable dt,string tableName ,string pkName)
		{
			if(pkName==String.Empty)
				return null;

			int rowCount=dt.Rows.Count;
			if(rowCount < 1)
			{
				return null;
			}
			
			string[] sqlString=new string[rowCount];
			DataRow row;

			
			for(int i= 0 ;i< rowCount;i++)	
			{
				row=dt.Rows[i];
				string updateString=GetUpdateSql(row,tableName,pkName);
				sqlString.SetValue(updateString,i);
			}
			return sqlString;
		}


		/// <summary>
		/// 得到更新数据库记录的sql语句，需要保证内存表的字段与数据库表的字段拼写一致。
		/// </summary>
		/// <param name="row">内存表中的一条记录。</param>
		/// <param name="tableName">需要更新的数据库表名。</param>
		/// <param name="pkName">主键。</param>
		/// <returns>成功返回sql语句，否则返回null。</returns>		
		public static string GetUpdateSql(DataRow row,string tableName ,string pkName)
		{
			if(pkName==String.Empty)
				return null;

			StringBuilder updateString=new StringBuilder("update "+tableName+"   set   ");			
			StringBuilder whereString=new StringBuilder("	  where		");
			int columnCount=row.Table.Columns.Count;

			for(int i=0;i<columnCount;i++)
			{
				string columnName=row.Table.Columns[i].ColumnName;
				string columnValue;
				switch(row[i].GetType().ToString())
				{
					case	"System.String":
						columnValue="'"+row[i].ToString()+"'";
						break;
					case	"System.DateTime":
						columnValue="'"+row[i].ToString()+"'";
						break;
					default:
						columnValue=row[i].ToString();
						break;
				}			
				
				if(pkName.CompareTo(columnName)!=-1)
				{
					whereString.Append( columnName  + "	=	" +columnValue);
				}
				else
				{
					updateString.Append( columnName +" = "+columnValue).Append(",");
				}						
			}
			
			updateString.Remove(updateString.Length-1,1).Append(whereString.ToString());						
			return updateString.ToString();
		}
		
		
		#endregion
		
		#region 得到需要执行Delete操作的Sql语句

		/// <summary>
		/// 得到删除数据库表记录的sql语句集，保证内存表的字段与数据库表的字段拼写一致。
		/// </summary>
		/// <param name="tableName">数据库表名。</param>
		/// <param name="pkName">主键集合。</param>
		/// <returns></returns>
		public	static string[]  GetDeleteSql(DataTable dt, string tableName,string[] pkName)
		{
			if (dt == null)
			{
				return null;
			}

			int rowCount = dt.Rows.Count;
			if (rowCount < 1)
			{
				return null;
			}

			string[] sqlString = new string[rowCount];
			int columnCount = dt.Columns.Count;
			
			if (pkName.Length > 0)
			{
				StringBuilder deleteString = new StringBuilder("delete from " + tableName + " where ");
				for(int i = 0; i < rowCount; i ++)
				{
					DataRow row = dt.Rows[i];

					for (int j = 0; j < columnCount; j ++)
					{
						string columnValue;
						switch (row[j].GetType().ToString())
						{
							case	"System.String":
								columnValue = "'" + row[j].ToString() + "'";
								break;
							case	"System.DateTime":
								columnValue = "'" + row[j].ToString() + "'";
								break;
							default:
								columnValue = row[j].ToString();
								break;
						}			

						string columnName = dt.Columns[j].ColumnName;
						for (int k = 0; k < pkName.Length; k ++)
						{
							if (pkName[k].CompareTo(columnName) != -1)
							{
								deleteString.Append(pkName[k] + " = " + columnValue + " and ");
								break;
							}
						}
					}
					deleteString.Remove(sqlString.Length - 4, 4);
					sqlString.SetValue(deleteString.ToString(), i);
				}
			}
			return sqlString;
		}
		

		/// <summary>
		/// 得到删除数据库表记录的sql语句，保证内存表的字段与数据库表的字段拼写一致。
		/// </summary>
		/// <param name="tableName">数据库表名。</param>
		/// <param name="pkName">主键。</param>
		/// <returns></returns>
		public	static string  GetDeleteSql(DataTable dt, string tableName, string pkName)
		{
			if (pkName.Length > 0)
			{
				StringBuilder sqlString = new StringBuilder("delete from " + tableName + " where " + pkName + " in (");
				for(int i = 0; i < dt.Columns.Count; i ++)
				{
					string columnName = dt.Columns[i].ColumnName;					
					if (pkName.CompareTo(columnName) != -1)
					{
						switch (dt.Columns[i].DataType.ToString())
						{
							case	"System.String":
								foreach (DataRow row in dt.Rows)
									sqlString.Append("'" + row[i].ToString() + "'").Append(", ");
								break;
							case	"System.DateTime":
								foreach (DataRow row in dt.Rows)
									sqlString.Append("'"+row[i].ToString()+"'").Append(", ");
								break;
							default:
								foreach (DataRow row in dt.Rows)
									sqlString.Append( row[i].ToString()).Append(", ");
								break;
						}                            
						break;
					}
				}
				return sqlString.Remove(sqlString.Length-2, 2).Append(")").ToString();
			}
			return null;
		}

		#endregion

		#region	解析查询的sql语句，构造weher条件

		/// <summary>
		/// 得到根据任意参数查询某一个数据库表记录的sql语句。
		/// </summary>
		/// <param name="tableName">数据库表名。</param>
		/// <param name="param">关于参数的哈希表，格式为数据库字段名、参数值，参数值的书写跟数据类型有关，比如字符型应该加''。</param>
		/// <returns></returns>
		public static string GetSelectSql(string tableName,Hashtable param)
		{
			StringBuilder sqlString = new StringBuilder("select *  from "+tableName);
			
			if (param.Count>0)
			{
				sqlString.Append("	where ");
				IDictionaryEnumerator myEnumerator = param.GetEnumerator();
				while ( myEnumerator.MoveNext() )
					sqlString.Append( myEnumerator.Key+"="+ myEnumerator.Value).Append("	and");

				sqlString.Remove(sqlString.Length-3,3);	
			}
			return sqlString.ToString();
		}

		#endregion
	}
}