想用asp.net 把 txt格式的手机号码数据文件导入到sql server 中,结果没弄成
先用winfrom导入吧
private void button1_Click(object sender, EventArgs e)
{
string strCnn = @"server=.\SQLEXPRESS; Integrated Security=true; Initial Catalog=xw";
FolderBrowserDialog dlg = new FolderBrowserDialog();
if (dlg.ShowDialog() != DialogResult.OK)
{
return;
}
string path = dlg.SelectedPath;//获取文件路径
using (SqlConnection conn = new SqlConnection(strCnn))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "delete from phonic";
cmd.ExecuteNonQuery();//执行非查询语句
}
}
string[] files = Directory.GetFiles(path, "*.txt", SearchOption.TopDirectoryOnly);
using (SqlConnection conn = new SqlConnection(strCnn))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "Insert into phonic(pNumber,shengfen,chengshi,yunyingshang) values(@pNumber,@shengfen,@chengshi,@yunyingshang)";
foreach (string file in files)
{
string[] lines = File.ReadAllLines(file, Encoding.Default);//不用StreamReader,因为文件很小
foreach (string line in lines)
{
string 号码 = "";
string 省份 = "";
string 城市 = "";
string 运营商 = "";
string[] strs = line.Split(' ');
int i = strs.Length;
if (i == 4)
{
号码 = strs[0];
省份 = strs[1];
城市 = strs[2];
运营商 = strs[3];
}
if (i == 3)
{
号码 = strs[0];
省份 = strs[1];
运营商 = strs[2];
城市 = " ";
}
cmd.Parameters.Clear();
cmd.Parameters.Add(new SqlParameter("pNumber", 号码));
cmd.Parameters.Add(new SqlParameter("shengfen", 省份));
cmd.Parameters.Add(new SqlParameter("chengshi", 城市));
cmd.Parameters.Add(new SqlParameter("yunyingshang", 运营商));
cmd.ExecuteNonQuery();
}
}
}
}
MessageBox.Show("数据导入成功");
}
导入成功后
protected void Button1_Click(object sender, EventArgs e)
{
string p= this.TextBox1.Text.Substring(0,7);
string strCnn = @"server=.\SQLEXPRESS; Integrated Security=true; Initial Catalog=xw";
SqlConnection conn = new SqlConnection(strCnn);
SqlCommand comm = new SqlCommand();
comm.CommandText = "select shengfen,chengshi,yunyingshang from phonic where pNumber=@p";
comm.Parameters.Clear();
comm.Parameters.Add(new SqlParameter("@p",p));
comm.Connection = conn;
SqlDataReader reader = null;
conn.Open();
reader = comm.ExecuteReader();
reader.Read();
this.province.Text = reader.GetString(reader.GetOrdinal("shengfen"));
this.city.Text = reader.GetString(reader.GetOrdinal("chengshi"));
this.ISP.Text = reader.GetString(reader.GetOrdinal("yunyingshang"));
reader.Close();
conn.Close();
}
功能是简单实现了,还没有优化,漏洞很多啊。