//string sql="Create table a (ClassID int identity(1,1), username varchar (50) not null, chatdate Datetime not null, chattalk varchar (5000) ) Alter table a add Constraint PK_a primary key clustered (ClassID,username) Grant All on a to public"; string sql=@"if not exists(select ID from Sysobjects where name='"+TextBox5.Text.Trim().ToString()+"' and Type='U') "; sql+="Create table "+TextBox5.Text.Trim().ToString()+" (ID int identity(1,1) , "+mytable.Columns[0].ColumnName+" varchar (50) not null, "+mytable.Columns[1].ColumnName+" varchar (50) not null, "+mytable.Columns[2].ColumnName+" varchar (50), "+mytable.Columns[3].ColumnName+" varchar (5000) ) Alter table "+TextBox5.Text.Trim().ToString()+" add Constraint PK_"+TextBox5.Text.Trim().ToString()+" primary key clustered (ID,"+mytable.Columns[0].ColumnName+") Grant All on "+TextBox5.Text.Trim().ToString()+" to public "; SqlConnection conn= new SqlConnection("user ID=sa;password=sa;database=mydb;server=172.18.27.103"); conn.Open(); SqlCommand com= new SqlCommand(sql,conn); com.ExecuteNonQuery(); conn.Close(); string str1="select * from "+TextBox5.Text.Trim().ToString()+" where 1=2"; DataSet ds= new DataSet(); SqlDataAdapter ada= new SqlDataAdapter(str1,conn); ada.Fill(ds," "+TextBox5.Text.Trim().ToString()+" "); str1=null; //導入資料到創建的表中 for(int i=0;i<DataGrid1.Items.Count;i++) { DataRow dr=ds.Tables[0].NewRow(); for(int a=0;a<mytable.Columns.Count ;a++) { dr[a+1]=DataGrid1.Items[i].Cells[a+2].Text;; } ds.Tables[0].Rows.Add(dr); } DataGrid1.DataSource=ds.Tables[0].DefaultView; DataGrid1.DataBind(); SqlCommandBuilder Builder= new SqlCommandBuilder(ada); ada.InsertCommand=Builder.GetInsertCommand(); ada.DeleteCommand=Builder.GetDeleteCommand(); ada.UpdateCommand=Builder.GetUpdateCommand(); ada.Update(ds," "+TextBox5.Text.Trim().ToString()+" "); DataGrid1.DataSource=ds.Tables[0].DefaultView; DataGrid1.DataBind(); }
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.OleDb;
using zjlweb;
namespace chat.data
{
/// <summary>
/// datalist 的摘要描述。
/// </summary>
public class datalist : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label Label1;
protected System.Web.UI.WebControls.Label Label2;
protected System.Web.UI.WebControls.Label Label3;
protected System.Web.UI.WebControls.TextBox TextBox4;
protected System.Web.UI.WebControls.Label Label4;
protected System.Web.UI.WebControls.Panel Panel1;
protected System.Web.UI.WebControls.DataGrid DataGrid1;
protected System.Web.UI.WebControls.Label Label5;
protected System.Web.UI.WebControls.TextBox TextBox5;
protected System.Web.UI.WebControls.TextBox adress;
protected System.Web.UI.WebControls.TextBox telephone;
protected System.Web.UI.WebControls.TextBox name;
protected System.Web.UI.WebControls.DropDownList sex;
protected System.Web.UI.WebControls.Button update;
protected System.Web.UI.WebControls.Button addnew;
protected System.Web.UI.WebControls.Button delete;
protected System.Web.UI.WebControls.DataGrid Datagrid2;
protected System.Web.UI.WebControls.Button save;
protected System.Web.UI.WebControls.Button query;
protected System.Web.UI.WebControls.DropDownList columns;
protected System.Web.UI.WebControls.Label Label6;
protected System.Web.UI.WebControls.DropDownList queryvalue;
protected System.Web.UI.WebControls.TextBox querytext;
protected System.Web.UI.WebControls.DataGrid DataGrid3;
protected System.Web.UI.WebControls.DropDownList DropDownList1;
protected System.Web.UI.WebControls.DropDownList DropDownList2;
protected System.Web.UI.HtmlControls.HtmlInputText tablename; public int index
{
get
{
object ob=ViewState["index"];
return (ob==null)?100:(int)ob;
}
set
{
ViewState["index"]=value;
}
} public DataTable mytable
{
get
{
object o=ViewState["mytable"];
return (o==null)?null:(DataTable)o;
}
set
{
ViewState["mytable"]=value;
}
}
public void Page_Load(object sender, System.EventArgs e)
{
// 將使用者程式碼置於此以初始化網頁
if (!IsPostBack)
{
this.bindgrid();
mytable=new DataTable();
mytable.Columns.Add("name", System.Type.GetType("System.String"));
mytable.Columns.Add("telephone",System.Type.GetType("System.String"));
mytable.Columns.Add("sex",System.Type.GetType("System.String"));
mytable.Columns.Add("adress",System.Type.GetType("System.String"));
DataGrid1.DataSource=mytable.DefaultView;
DataGrid1.DataBind();
RegisterStartupScript("focus", "<script>document.all('name').focus()</script>");
this.addcolumns();
}
}
private void bindgrid()
{
SqlConnection conn= new SqlConnection("user id=sa;password=sa;database=mydb;server=172.18.17.13");
conn.Open();
string sql="select * from chattest";
SqlCommand com=new SqlCommand(sql,conn);
SqlDataAdapter da= new SqlDataAdapter(sql,conn);
SqlCommandBuilder cb= new SqlCommandBuilder(da);
DataSet ds=new DataSet();
da.Fill(ds,"chattest"); } private void addcolumns()
{
for(int i=0;i<mytable.Columns.Count;i++)
{
columns.Items.Add(mytable.Columns[i].ColumnName.ToString());
}
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 此呼叫為 ASP.NET Web Form 設計工具的必要項。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 此為設計工具支援所必需的方法 - 請勿使用程式碼編輯器修改
/// 這個方法的內容。
/// </summary>
private void InitializeComponent()
{
this.DataGrid1.ItemCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid1_ItemCommand);
this.DataGrid1.SortCommand += new System.Web.UI.WebControls.DataGridSortCommandEventHandler(this.DataGrid1_SortCommand);
this.save.Click += new System.EventHandler(this.save_Click);
this.update.Click += new System.EventHandler(this.update_Click);
this.addnew.Click += new System.EventHandler(this.addnew_Click);
this.delete.Click += new System.EventHandler(this.delete_Click);
this.TextBox5.TextChanged += new System.EventHandler(this.TextBox5_TextChanged);
this.query.Click += new System.EventHandler(this.query_Click);
this.columns.SelectedIndexChanged += new System.EventHandler(this.columns_SelectedIndexChanged);
this.queryvalue.SelectedIndexChanged += new System.EventHandler(this.queryvalue_SelectedIndexChanged);
this.Load += new System.EventHandler(this.Page_Load); }
#endregion private void addnew_Click(object sender, System.EventArgs e)
{
// for(int i=0;i<DataGrid1.Items.Count;i++)
// {
// DataRow row=mytable.NewRow();
// for (int b=0;b<mytable.Columns.Count;b++)
// {
// row[b]=DataGrid1.Items[i].Cells[b].Text;
// }
// mytable.Rows.Add(row);
// }
// // }
DataRow dr=mytable.NewRow();
if (name.Text!="")
{
dr["name"]=name.Text;
}
else
{
dr["name"]="zhangjinlin";
}
if (telephone.Text!="")
{
dr["telephone"]=telephone.Text;
}
else
{
dr["telephone"]="13925308091";
}
if (adress.Text!="")
{
dr["adress"]=adress.Text;
}
else
{
dr["adress"]="中山三鄉寶元WEB組";
}
if (sex.SelectedItem.Text!="---select---")
{
dr["sex"]=sex.SelectedItem.Text;
}
else
{
dr["sex"]="男";
}
mytable.Rows.Add(dr);
DataGrid1.DataSource=mytable.DefaultView;
DataGrid1.DataBind();
} private void delete_Click(object sender, System.EventArgs e)
{
if (mytable.Rows.Count!=0)
{
mytable.Clear();
zjlweb.data.message alert=new zjlweb.data.message();
alert.Show("資料表中資料已被刪除﹗",this.Page);
mytable.Clear();
}
else
{
zjlweb.data.message alert=new zjlweb.data.message();
alert.Show("資料表中已無資料﹐請繼續作業﹗",this.Page);
}
DataGrid1.DataSource=mytable.DefaultView;
DataGrid1.DataBind();
} private void save_Click(object sender, System.EventArgs e)
{
if (TextBox5.Text=="")
{
zjlweb.data.message msg=new zjlweb.data.message();
msg.Show("創建資料表名不能為空﹗",this.Page);
RegisterStartupScript("focus", "<script>document.all('textbox5').focus()</script>");
}
else
{
//string sql="Create table a (ClassID int identity(1,1), username varchar (50) not null, chatdate Datetime not null, chattalk varchar (5000) ) Alter table a add Constraint PK_a primary key clustered (ClassID,username) Grant All on a to public";
string sql=@"if not exists(select ID from Sysobjects where name='"+TextBox5.Text.Trim().ToString()+"' and Type='U') ";
sql+="Create table "+TextBox5.Text.Trim().ToString()+" (ID int identity(1,1) , "+mytable.Columns[0].ColumnName+" varchar (50) not null, "+mytable.Columns[1].ColumnName+" varchar (50) not null, "+mytable.Columns[2].ColumnName+" varchar (50), "+mytable.Columns[3].ColumnName+" varchar (5000) ) Alter table "+TextBox5.Text.Trim().ToString()+" add Constraint PK_"+TextBox5.Text.Trim().ToString()+" primary key clustered (ID,"+mytable.Columns[0].ColumnName+") Grant All on "+TextBox5.Text.Trim().ToString()+" to public ";
SqlConnection conn= new SqlConnection("user ID=sa;password=sa;database=mydb;server=172.18.27.103");
conn.Open();
SqlCommand com= new SqlCommand(sql,conn);
com.ExecuteNonQuery();
conn.Close(); string str1="select * from "+TextBox5.Text.Trim().ToString()+" where 1=2";
DataSet ds= new DataSet();
SqlDataAdapter ada= new SqlDataAdapter(str1,conn);
ada.Fill(ds," "+TextBox5.Text.Trim().ToString()+" ");
str1=null; //導入資料到創建的表中
for(int i=0;i<DataGrid1.Items.Count;i++)
{
DataRow dr=ds.Tables[0].NewRow();
for(int a=0;a<mytable.Columns.Count ;a++)
{ dr[a+1]=DataGrid1.Items[i].Cells[a+2].Text;;
}
ds.Tables[0].Rows.Add(dr);
}
DataGrid1.DataSource=ds.Tables[0].DefaultView;
DataGrid1.DataBind(); SqlCommandBuilder Builder= new SqlCommandBuilder(ada);
ada.InsertCommand=Builder.GetInsertCommand();
ada.DeleteCommand=Builder.GetDeleteCommand();
ada.UpdateCommand=Builder.GetUpdateCommand();
ada.Update(ds," "+TextBox5.Text.Trim().ToString()+" "); DataGrid1.DataSource=ds.Tables[0].DefaultView;
DataGrid1.DataBind();
}
} private void TextBox5_TextChanged(object sender, System.EventArgs e)
{
if (TextBox5.Text!="")
{
string str=TextBox5.Text;
TextBox5.Text="PY"+str;
}
} protected void DataGrid1_ItemCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
index=e.Item.ItemIndex;
if (e.CommandName=="edit")
{
name.Text=e.Item.Cells[2].Text;
telephone.Text=e.Item.Cells[3].Text;
adress.Text=e.Item.Cells[5].Text;
sex.SelectedItem.Text=e.Item.Cells[4].Text;
}
if (e.CommandName=="delete")
{
mytable.Rows[(int)e.Item.ItemIndex].Delete();
DataGrid1.DataSource=mytable.DefaultView;
DataGrid1.DataBind();
}
}
{
if (index!=100)
{
DataRow dr=mytable.Rows[index];
dr["name"]=name.Text;
dr["telephone"]=telephone.Text;
dr["sex"]=sex.SelectedItem.Text;
dr["adress"]=adress.Text;
mytable.AcceptChanges();
DataGrid1.DataSource=mytable.DefaultView;
DataGrid1.DataBind();
}
} private void DataGrid1_SortCommand(object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
{
mytable.DefaultView.Sort=e.SortExpression.ToString();
DataGrid1.DataSource=mytable.DefaultView;
DataGrid1.DataBind();
} private void columns_SelectedIndexChanged(object sender, System.EventArgs e)
{
switch (columns.SelectedItem.Text)
{
case "name":
queryvalue.Items.Clear();
for(int i=0;i<mytable.Rows.Count;i++)
{
queryvalue.Items.Add(mytable.Rows[i][0].ToString());
}
break;
case "telephone":
queryvalue.Items.Clear();
for(int i=0;i<mytable.Rows.Count;i++)
{
queryvalue.Items.Add(mytable.Rows[i][1].ToString());
}
break;
case "sex":
queryvalue.Items.Clear();
for(int i=0;i<mytable.Rows.Count;i++)
{
queryvalue.Items.Add(mytable.Rows[i][2].ToString());
}
break;
case "adress":
queryvalue.Items.Clear();
for(int i=0;i<mytable.Rows.Count;i++)
{
queryvalue.Items.Add(mytable.Rows[i][3].ToString());
}
break;
default:
Response.Write("<script>alert('數據選擇錯誤!請選擇一個數據后進行作業!')</script>");
break;
} } private void query_Click(object sender, System.EventArgs e)
{
// string sql="select * from mytable where "+columns.SelectedItem.Text+"="+queryvalue.SelectedItem.Text+" ";
string sql="select * from mytable where "+columns.SelectedItem.Text+"='"+queryvalue.SelectedItem.Text+"' ";
SqlConnection conn= new SqlConnection("user ID=webnewuser;password=@webtest;database=webnewuser;server=172.18.27.103");
SqlDataAdapter ada=new SqlDataAdapter(sql,conn);
DataTable dt=new DataTable();
DataSet ds=new DataSet();
ada.Fill(ds,"dt ");
DataGrid3.DataSource=ds.Tables[0].DefaultView;
DataGrid3.DataBind();
}
oleDbCommandBuilder olcmd=new oleDbCommandBuilder(oleDbDataAdapter1);就可以了