之前用stringbuilder动态建sql的时候，写过这样的sql
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" INSERT INTO ");
sqlBuilder.Append(" @TABLENAME ");
sqlBuilder.Append(" (AppID,Buffer) ");
sqlBuilder.Append(" VALUES( ");
sqlBuilder.Append(" @APPID, @BUFFER ");
sqlBuilder.Append(" ) ");
sqlStr = sqlBuilder.ToString();
ArrayList paramList = new ArrayList();
paramList.Add(new SqlParameter("@TABLENAME", Table));
paramList.Add(new SqlParameter("@APPID", Appl));
paramList.Add(new SqlParameter("@BUFFER", Buffer));
................
然后运行报错：
{"The variable name '@TABLENAME' has already been declared. Variable names must be unique within a query batch or stored procedure.\r\nMust declare the table variable \"@TABLENAME\"."}
System.Exception {System.Data.SqlClient.SqlException}
+ e {"Must declare the table variable \"@TABLENAME\"."} System.Exception {System.Data.SqlClient.SqlException}
想起过去也遇到过类似的情况，当时是在order by子句里用过@变量做参数，也是值不进去 - =
后来这么改写之后就没问题了——
sqlBuilder.Append(" INSERT INTO ");
sqlBuilder.AppendFormat(" {0} ", Table);
sqlBuilder.Append(" ([AppID],[Action],[Group],[Buffer],[Operator],[TimeStamp]) ");
sqlBuilder.Append(" VALUES( ");
sqlBuilder.Append(" @APPID, @ACTION, @GROUP, @BUFFER, @USER, @TIME ");
sqlBuilder.Append(" ) ");
虽然问题解决了，可是到现在也不知道原因是什么，难道这种方式只适用于在where子句里使用？囧
另，还见到有人这么写——
string insertSql = string.Format("INSERT INTO [{0}]({1}) VALUES({2})", destTableName, sbFields, sbParams);
http://www.cnblogs.com/upto/articles/1066400.html
自动生成Insert 语句的小工具 http://kb.cnblogs.com/a/1581732/ 这个看起来蛮有意思，哪天有空仔细瞅瞅
StringBuilder动态建表与插数据
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Data;
// 连接字符串，可修改
string ConnectionString = " Data Source=A-FA8619ED2B234\\SQLEXPRESS;Initial Catalog=test;User ID=wxg;Password=test " ;
/// <summary>
/// 判断是否存在表
/// </summary>
/// <param name="table"> 表名 </param>
/// <returns></returns>
public bool HasTable( string table)
{
SqlConnection conn = new SqlConnection(ConnectionString);
string strSql = " select * from dbo.sysobjects where id = object_id(N'[dbo].[ " + table + " ]') and OBJECTPROPERTY(id, N'IsUserTable') = 1 " ;
conn.Open();
SqlCommand cmd = new SqlCommand(strSql, conn);
SqlDataReader dr = cmd.ExecuteReader();
return dr.Read();
}
/// <summary>
/// 动态建表
/// </summary>
/// <param name="ProjectID"> 以项目号作为表名,如123 </param>
/// <param name="PollID"> 问卷编号 </param>
/// <param name="Answer"> 答案列名 </param>
/// <param name="Len"> 全部列的长度数组 </param>
public void CreateTable( string ProjectID, string PollID, string Answer, int [] Len)
{
SqlConnection conn = new SqlConnection(ConnectionString);
// 如果有同名表存在
if (HasTable(ProjectID))
{
MessageBox.Show( " 表 " + ProjectID + " 已经存在 " );
}
else
{
// 创建表
StringBuilder strSql = new StringBuilder();
StringBuilder Column = new StringBuilder();
string Col = "" ;
strSql.Append( " create table " );
strSql.Append( " [ " );
strSql.Append( "" + ProjectID + "" );
strSql.Append( " ] " );
strSql.Append( " ( " );
strSql.Append( " " + PollID + " varchar(50) primary key, " );
for ( int i = 0 ; i < Len.Length; i ++ )
{
int t = i + 1 ;
string An = Answer + t;
Column.Append( "" + An + " varchar( " + Len[i] + " ) , " );
}
if (Column.Length != 0 )
{
Col = Column.ToString().Substring( 0 , Column.Length - 1 );
}
strSql.Append(Col);
strSql.Append( " ); " );
conn.Open();
SqlCommand cmd = new SqlCommand(strSql.ToString(), conn);
cmd.ExecuteNonQuery();
MessageBox.Show( " 创建 " + ProjectID + " 表成功 " );
conn.Close();
}
}
/// <summary>
/// 插入数据到表中
/// </summary>
/// <param name="ProjectID"> 以项目号作为表名,如123 </param>
/// <param name="PollID"> 问卷编号 </param>
/// <param name="Answer"> 答案数组 </param>
public void InsertData( string ProjectID, string PollID, string [] Answer)
{
SqlConnection conn = new SqlConnection(ConnectionString);
StringBuilder strSql = new StringBuilder();
StringBuilder Values = new StringBuilder();
string Va = "" ;
strSql.Append( " insert into " );
strSql.Append( " [ " );
strSql.Append( "" + ProjectID + "" );
strSql.Append( " ] " );
strSql.Append( " values " );
strSql.Append( " ( " );
strSql.Append( " ' " + PollID + " ', " );
for ( int i = 0 ; i < Answer.Length; i ++ )
{
Values.Append( " ' " + Answer[i] + " ' , " );
}
if (Values.Length != 0 )
{
Va = Values.ToString().Substring( 0 , Values.Length - 1 );
}
strSql.Append(Va);
strSql.Append( " ); " );
conn.Open();
SqlCommand cmd = new SqlCommand(strSql.ToString(), conn);
cmd.ExecuteNonQuery();
System.Web.HttpContext.Current.Response.Write( " <script>alert('插入数据到 " + ProjectID + " 表中成功')</script> " );
conn.Close();
}
// **************导出数据到TXT文件
/// <summary>
/// 直接用表名导出数据到同名TXT文件
/// </summary>
/// <param name="ProjectID"> 项目号也是表名,如123 </param>
/// <param name="TXTPATH"> 导出TXT文件目录，格式如 @"G:\"; </param>
public void ToTxt( string ProjectID, string TXTPATH)
{
// 如果表中有数据，则导出数据
if (HasData(ProjectID))
{
DataSet ds = GetData(ProjectID);
ExportToTxt(ds, TXTPATH, ProjectID);
}
else
{
System.Web.HttpContext.Current.Response.Write( " 表中没有数据 " );
}
}
/// <summary>
/// 判断表中是否有数据
/// </summary>
/// <param name="table"> 表名 </param>
public bool HasData( string table)
{
SqlConnection conn = new SqlConnection(ConnectionString);
string strSql = " select * from [ " + table + " ] " ;
conn.Open();
SqlCommand cmd = new SqlCommand(strSql, conn);
SqlDataReader dr = cmd.ExecuteReader();
return dr.Read();
}
/// <summary>
/// 获得数据集Dataset
/// </summary>
/// <param name="table"> 表名 </param>
/// <returns> Dataset </returns>
public DataSet GetData( string table)
{
try
{
string strSql = " select * from [ " + table + " ] " ;
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(strSql, conn);
DataSet ds = new DataSet();
da.Fill(ds, "" + table + "" );
return ds;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 把数据导出到txt文件
/// </summary>
/// <param name="ds"> 数据集Dataset </param>
/// <param name="TXTPATH"> txt文件目录 </param>
/// <param name="ProjectID"> 以项目名作为文件名 </param>
public void ExportToTxt(DataSet ds, string TXTPATH, string ProjectID )
{
string TXTPOSTFIX = " .txt " ;
if (ds.Tables.Count != 0 )
{
// 创建一个.txt文件，文件名用项目名
FileInfo file = new FileInfo( "" + TXTPATH + ProjectID + TXTPOSTFIX + "" );
StreamWriter textFile = null ;
try
{
textFile = file.CreateText();
}
catch
{
System.Web.HttpContext.Current.Response.Write( " 系统找不到指定目录下的文件： " + TXTPATH + ProjectID + TXTPOSTFIX + " " );
return ;
}
// 把Dataset中的数据写入.txt文件中
for ( int totaltable = 0 ; totaltable < ds.Tables.Count; totaltable ++ )
{
// 统计dataset中当前表的行数
int row = ds.Tables[totaltable].Rows.Count;
// 统计dataset中当前表的列数
int column = ds.Tables[totaltable].Columns.Count;
// 用于统计当前表中每列记录中字符数最长的字符串的长度之和
int totalLength = 0 ;
// 把dataset中当前表的数据写入.txt文件中
for ( int i = 0 ; i < row; i ++ )
{
for ( int j = 0 ; j < column; j ++ )
{
textFile.Write(ds.Tables[totaltable].Rows[i][j].ToString());
}
textFile.WriteLine();
}
textFile.WriteLine();
}
// 关闭当前的StreamWriter流
textFile.Close();
System.Web.HttpContext.Current.Response.Write( " 数据文件已保存到 " + " " + file.FullName);
}
else
{
System.Web.HttpContext.Current.Response.Write( " 表中没有数据 " );
}
}
}
取电话号码后10位数字，可是电话号码字符串里可能包含有不定位数的其他字符，所以要先滤掉非数字的字符，只留下数字，然后再截取。
string strPhone = xxxxx;
string tmpPhone = "" ;
Regex reg = new Regex( @" \d+(?:\.\d+)? " );
Match m = reg.Match(strPhone, 0 ); //从第0个开始检查
for (m = reg.Match(strPhone); m.Success; m = m.NextMatch())
{
tmpPhone += m.Value;
}
strPhone = tmpPhone.Substring(tmpPhone.Length - 10 , 10 ); //取电话号码后10位
脏字过滤 http://it-worker.blogbus.com/logs/66665795.html
[转]C#中Trim()、TrimStart()、TrimEnd()的错误认识
如果这三个函数带上字符型数组的参数，则是删除字符型数组中出现的任意字符。如Trim("abcd".ToCharArray())就是删除字符串头部及尾部出现的a或b或c或d字符，删除的过程直到碰到一个既不是a也不是b也不是c也不是d的字符才结束。
这里最容易引起的误会就是以为删除的是"abcd"字符串。如下例：
string s = " from dual union all ";
s = s.Trim().TrimEnd("union all".ToCharArray());
可能有人以为上面s的最终结果是"from dual"，但真正的结果是"from d"。需要注意的是这种写法执行的删除对象是字符数组中出现的任意字符，而不是这些字符连在一起组成的字符串！
522
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
