WCF开发(7) 查询SQLServer数据库（简单实现）

1.写服务（服务端）

 

[ServiceContract]
    public interface ISQLServerService
    {
        [OperationContract]
        SQLResult Query(SQLInfo info);

        [OperationContract]
        SQLResult Execute(SQLInfo info);
    }

    public class SQLServerService : ISQLServerService
    {
        public SQLResult Execute(SQLInfo info)
        {
            SQLResult result = new SQLResult();
            string connectString = info.Server.GetConnectionString();
            using (SqlConnection connection = new SqlConnection(connectString))
            {
                try
                {
                    connection.Open();
                    SqlCommand sqlCmd = new SqlCommand(info.Sql, connection);
                    int rowCount=sqlCmd.ExecuteNonQuery();
                    connection.Close();
                    result.Data = rowCount.ToString();
                }
                catch (Exception ex)
                {
                    result.ErrorMessage = ex.ToString();
                }
            }
            return result;
        }

        public SQLResult Query(SQLInfo info)
        {
            SQLResult result = new SQLResult();
            string connectString = info.Server.GetConnectionString();
            using (SqlConnection connection = new SqlConnection(connectString))
            {
                try
                {
                    connection.Open();
                    SqlDataAdapter sqlDa = new SqlDataAdapter(info.Sql, connection);
                    DataTable dt = new DataTable();
                    dt.TableName = info.TableName;
                    if (string.IsNullOrEmpty(dt.TableName))
                    {
                        dt.TableName = "DataTable";
                    }
                    sqlDa.Fill(dt);
                    connection.Close();

                    System.IO.TextWriter tw = new System.IO.StringWriter();
                    dt.WriteXml(tw);
                    string xml = tw.ToString();
                    result.Data = xml;
                }
                catch (Exception ex)
                {
                    result.ErrorMessage = ex.ToString();
                }
            }
            return result;
        }
    }

[DataContract]
    public class SQLInfo
    {
        [DataMember]
        public SQLServerDb Server { get; set; }

        [DataMember]
        public string Sql { get; set; }

        [DataMember]
        public string TableName { get; set; }
    }

    [DataContract]
    public class SQLResult
    {
        [DataMember]
        public string Data { get; set; }

        [DataMember]
        public string ErrorMessage { get; set; }
    }

    [DataContract]
    public class SQLServerDb
    {
        [DataMember]
        public string Server { get; set; }
        [DataMember]
        public string Database { get; set; }
        [DataMember]
        public string User { get; set; }
        [DataMember]
        public string Password { get; set; }

        public string GetConnectionString()
        {
            //"Server = 192.168.1.155,1433; Database = IDC_Esop; Persist Security Info = true; User ID = sa; Password = 123456; Connect Timeout = 10;"
            return string.Format("Server = {0},1433; Database = {1}; Persist Security Info = true; User ID = {2}; Password = {3}; Connect Timeout = 10;", Server, Database, User, Password);
        }
    }

2.启动发布服务（服务端）

3.引用服务（客户端）

4.调用服务（客户端）

private void BtnQuerySql_Click(object sender, RoutedEventArgs e)
        {
            string sql = TbSql.Text;
            QuerySql(sql);
        }

        private void BtnExecuteSql_Click(object sender, RoutedEventArgs e)
        {
            ExecuteSql(TbSql.Text);
        }

        private static SQLServerDb GetSQLServerDb()
        {
            SQLServerDb sqlDb = new SQLServerDb();
            sqlDb.Server = "192.168.1.155";
            sqlDb.Database = "topviewxp";
            sqlDb.User = "sa";
            sqlDb.Password = "123456";
            return sqlDb;
        }

        private async void QuerySql(string sql)
        {
            try
            {
                SQLInfo info = new SQLInfo();
                info.Server = GetSQLServerDb();
                info.Sql = sql;
                info.TableName = "NameToType";
                SQLServerServiceClient sqlClient = GetSQLServerServiceClient();
                SQLResult result = await sqlClient.QueryAsync(info);
                TBResult.Text = result.GetText();
            }
            catch (Exception ex)
            {
                TBResult.Text = ex.ToString();
            }
        }

        private async void ExecuteSql(string sql)
        {
            try
            {
                SQLInfo info = new SQLInfo();
                info.Server = GetSQLServerDb();
                info.Sql = sql;
                info.TableName = "NameToType";
                SQLServerServiceClient sqlClient = GetSQLServerServiceClient();
                SQLResult result = await sqlClient.ExecuteAsync(info);
                TBResult.Text = result.GetText();
            }
            catch (Exception ex)
            {
                TBResult.Text = ex.ToString();
            }
        }

        public SQLServerServiceClient GetSQLServerServiceClient()
        {
            //return new SQLServerServiceClient();

            string ip = CbIpList.SelectedItem.ToString();
            NetTcpBinding binding = new NetTcpBinding(SecurityMode.None);
            binding.MaxReceivedMessageSize = int.MaxValue;
            EndpointAddress endpointAddress = new EndpointAddress("net.tcp://" + ip + ":8001/SQLServerService");
            SQLServerServiceClient currentClient = new SQLServerServiceClient(binding, endpointAddress);
            return currentClient;
        }

5.结果

6.hololens测试：没问题

7.后续：1.界面修改，能够查询所有表。2.用EF

这就是一次简单的WCF的应用